Re: $$Excel-Macros$$ Date from textbox reversed.
Thank you - both work. and also thanks to Paul for helping me to shorten my code However, here is an interesting fact If in textbox 1 I enter 12/9/11 the text in the worksheet shows a 9/12/2011 but if i enter 12/9/2011 then the text shows as 12/9/2011 On Sep 9, 7:52 pm, Paul Schreiner schreiner_p...@att.net wrote: How is the date entered into the textbox? perhaps Excel isn't recognizing the value as an actual date... Try this:] if (isdate(frmApplications.TextBox1.Value)) then ActiveCell.Value = Format(frmApplications.TextBox1.Value)) , dd/mm/) end if I'm suspecting that the value in the textbox is being entered as a string that Excel isn't recognizing as a date, so it enters it as Text. also.. You don't really have to do all of the selects... ActiveCell.Value = Date ActiveCell.Offset(0, 1).Value = FrmApplications.TextBox1.Value should work just as well. Paul - “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - From: Brian brianfosterbl...@gmail.com To: MS EXCEL AND VBA MACROS excel-macros@googlegroups.com Sent: Fri, September 9, 2011 12:56:17 PM Subject: $$Excel-Macros$$ Date from textbox reversed. I am using a form to enter data and then from the textbox complete data in an excel spreadsheet. This is my code ActiveCell.Value = Date ActiveCell.Offset(0, 1).Select ActiveCell.Value = FrmApplications.TextBox1.Value ActiveCell.Offset(0, 1).Select Activecell.Value = Date works perfectly as it places the date in column A correctly in the format set for my computer dd/mm/ In textbox 1 I am entering a date in the form but when this is pulled into the cell in column B it reverses the date to mm/dd/ although my windows setting has been set to the format that appears in colmn A How do I correct this? I am using Excel 2007 -- ------ Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links :http://twitter.com/exceldailytip 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials athttp://www.excel-macros.blogspot.com 4. Learn VBA Macros athttp://www.quickvba.blogspot.com 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below linkhttp://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Date from textbox reversed.
I think the problem is related to the string-to-date conversion. In a userform textbox, the characters you enter are string characters. When you enter that string into a cell, Excel attempts to determine if the string is a date. If it determines that it is an actual date, it calculates the number of days from this date and 1/1/1900 and enters this number into the cell. This number can be DISPLAYED in a large number of combinations: Monday, September 12, 2011 12-Sep-2011 Sep-11 This is possible because this date is actually a NUMBER, displayed in different formats. The default Windows date format is merely a starting point. Excel uses this to determine if 12/9/2011 is September 12, or December 9. But the actual formatting of the cell is NOT directly related to the Windows date format. What MAY be happening here is that Excel is interpreting 12/9/2011 as a string and is not converting it to a date format. I suggest that you utilize functions like DateValue(), Format() and isdate() to FORCE the string being inserted into Excel to a specific format. That is: if 12/9/11 works correctly, then use something like: ActiveCell.Offset(0, 1).Value = format(FrmApplications.TextBox1.Value,dd/mm/yy) hope this helps, Paul - “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - From: Brian brianfosterbl...@gmail.com To: MS EXCEL AND VBA MACROS excel-macros@googlegroups.com Sent: Tue, September 13, 2011 11:38:50 AM Subject: Re: $$Excel-Macros$$ Date from textbox reversed. Thank you - both work. and also thanks to Paul for helping me to shorten my code However, here is an interesting fact If in textbox 1 I enter 12/9/11 the text in the worksheet shows a 9/12/2011 but if i enter 12/9/2011 then the text shows as 12/9/2011 On Sep 9, 7:52 pm, Paul Schreiner schreiner_p...@att.net wrote: How is the date entered into the textbox? perhaps Excel isn't recognizing the value as an actual date... Try this:] if (isdate(frmApplications.TextBox1.Value)) then ActiveCell.Value = Format(frmApplications.TextBox1.Value)) , dd/mm/) end if I'm suspecting that the value in the textbox is being entered as a string that Excel isn't recognizing as a date, so it enters it as Text. also.. You don't really have to do all of the selects... ActiveCell.Value = Date ActiveCell.Offset(0, 1).Value = FrmApplications.TextBox1.Value should work just as well. Paul - “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - From: Brian brianfosterbl...@gmail.com To: MS EXCEL AND VBA MACROS excel-macros@googlegroups.com Sent: Fri, September 9, 2011 12:56:17 PM Subject: $$Excel-Macros$$ Date from textbox reversed. I am using a form to enter data and then from the textbox complete data in an excel spreadsheet. This is my code ActiveCell.Value = Date ActiveCell.Offset(0, 1).Select ActiveCell.Value = FrmApplications.TextBox1.Value ActiveCell.Offset(0, 1).Select Activecell.Value = Date works perfectly as it places the date in column A correctly in the format set for my computer dd/mm/ In textbox 1 I am entering a date in the form but when this is pulled into the cell in column B it reverses the date to mm/dd/ although my windows setting has been set to the format that appears in colmn A How do I correct this? I am using Excel 2007 -- ------ - Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links :http://twitter.com/exceldailytip 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials athttp://www.excel-macros.blogspot.com 4. Learn VBA Macros athttp://www.quickvba.blogspot.com 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below linkhttp://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros
$$Excel-Macros$$ Date from textbox reversed.
I am using a form to enter data and then from the textbox complete data in an excel spreadsheet. This is my code ActiveCell.Value = Date ActiveCell.Offset(0, 1).Select ActiveCell.Value = FrmApplications.TextBox1.Value ActiveCell.Offset(0, 1).Select Activecell.Value = Date works perfectly as it places the date in column A correctly in the format set for my computer dd/mm/ In textbox 1 I am entering a date in the form but when this is pulled into the cell in column B it reverses the date to mm/dd/ although my windows setting has been set to the format that appears in colmn A How do I correct this? I am using Excel 2007 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Date from textbox reversed.
Hi Try ActiveCell.Value = Format(FrmApplications.TextBox1.Value,dd/mm/) Thanks Mahesh On Fri, Sep 9, 2011 at 10:26 PM, Brian brianfosterbl...@gmail.com wrote: I am using a form to enter data and then from the textbox complete data in an excel spreadsheet. This is my code ActiveCell.Value = Date ActiveCell.Offset(0, 1).Select ActiveCell.Value = FrmApplications.TextBox1.Value ActiveCell.Offset(0, 1).Select Activecell.Value = Date works perfectly as it places the date in column A correctly in the format set for my computer dd/mm/ In textbox 1 I am entering a date in the form but when this is pulled into the cell in column B it reverses the date to mm/dd/ although my windows setting has been set to the format that appears in colmn A How do I correct this? I am using Excel 2007 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Date from textbox reversed.
How is the date entered into the textbox? perhaps Excel isn't recognizing the value as an actual date... Try this:] if (isdate(frmApplications.TextBox1.Value)) then ActiveCell.Value = Format(frmApplications.TextBox1.Value)) , dd/mm/) end if I'm suspecting that the value in the textbox is being entered as a string that Excel isn't recognizing as a date, so it enters it as Text. also.. You don't really have to do all of the selects... ActiveCell.Value = Date ActiveCell.Offset(0, 1).Value = FrmApplications.TextBox1.Value should work just as well. Paul - “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - From: Brian brianfosterbl...@gmail.com To: MS EXCEL AND VBA MACROS excel-macros@googlegroups.com Sent: Fri, September 9, 2011 12:56:17 PM Subject: $$Excel-Macros$$ Date from textbox reversed. I am using a form to enter data and then from the textbox complete data in an excel spreadsheet. This is my code ActiveCell.Value = Date ActiveCell.Offset(0, 1).Select ActiveCell.Value = FrmApplications.TextBox1.Value ActiveCell.Offset(0, 1).Select Activecell.Value = Date works perfectly as it places the date in column A correctly in the format set for my computer dd/mm/ In textbox 1 I am entering a date in the form but when this is pulled into the cell in column B it reverses the date to mm/dd/ although my windows setting has been set to the format that appears in colmn A How do I correct this? I am using Excel 2007 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel