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/yyyy") > 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/yyyy > 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/yyyy 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 -- ---------------------------------------------------------------------------------- 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
