Date format issue when submitting from a userform to a spreadsheet

Hi,

I have a userform that I've generated which routes dates onto a spreadsheet 
based on the users input.  I am having a bit of a frustrating time with the 
dates, it would appear that in the process of moving the date from the 
userform to the spreadsheet some dates are switched/transposed.  I'll give an 
example.  If someone enters 09/02/2004 on the userform excel seems to look at 
09 and assume it is a month and transposes the dates to 02/09/2004(this does 
not appear to be a US/UK format issues as I have already gone down that 
road).  However, if the date 13/09/2004 is entered into the userform no 
change occurs. the format remains integral.  It would appear that if the 
first "day" figure is less than 12 excel transposes the date but if that 
"day" figure is more than twelve it doesn't.  I am aware of a similar issue 
with merging dates to excel to word but have not yet come across this within 
excel itself.  Does anyone have a possible suggestion of a way around this?  

Many thanks
-- 
Andy T
0
AndyT (25)
11/3/2004 5:17:35 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
986 Views

Similar Articles

[PageSpeed] 28

Hi Andy,

> I have a userform that I've generated which routes dates onto a spreadsheet 
> based on the users input.  I am having a bit of a frustrating time with the 
> dates, it would appear that in the process of moving the date from the 
> userform to the spreadsheet some dates are switched/transposed.  I'll give an 
> example.  If someone enters 09/02/2004 on the userform excel seems to look at 
> 09 and assume it is a month and transposes the dates to 02/09/2004(this does 
> not appear to be a US/UK format issues as I have already gone down that 
> road).  However, if the date 13/09/2004 is entered into the userform no 
> change occurs. the format remains integral.  It would appear that if the 
> first "day" figure is less than 12 excel transposes the date but if that 
> "day" figure is more than twelve it doesn't.  I am aware of a similar issue 
> with merging dates to excel to word but have not yet come across this within 
> excel itself.  Does anyone have a possible suggestion of a way around this?

Sure. Instead of just assigning the text of the text box to the cell (letting 
Excel do any conversions), explicitly convert it yourself:

Instead of:
 Range("A1").Value = TextBox1.Text

Use:
 Range("A1").Value = CLng(TextBox1.Text)

For (much) more information about Excel's international issues, see 
http://www.oaltd.co.uk/ExcelProgRef/Ch22/

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk


0
11/3/2004 5:54:51 PM
Thanks Stephen,

I think this is the solution but I am struggling with the code I am using.  
Currently I am using two commands, one to determine the next available row on 
the spreadsheet and one to transfer the data, thus

NextRow = Application.WorksheetFunction.CountA(Range("A:A")) + 1

and

Cells (NextRow ,1) = TextBox1.Text

If I change the =TextBox1.Text to =CLng(TextBox1.Text) I get a Runtime Error 
13. Forgive me but my knowledge here is scant, perhaps you could let me know 
where I am going wrong.

Many thanks

"Stephen Bullen" wrote:

> Hi Andy,
> 
> > I have a userform that I've generated which routes dates onto a spreadsheet 
> > based on the users input.  I am having a bit of a frustrating time with the 
> > dates, it would appear that in the process of moving the date from the 
> > userform to the spreadsheet some dates are switched/transposed.  I'll give an 
> > example.  If someone enters 09/02/2004 on the userform excel seems to look at 
> > 09 and assume it is a month and transposes the dates to 02/09/2004(this does 
> > not appear to be a US/UK format issues as I have already gone down that 
> > road).  However, if the date 13/09/2004 is entered into the userform no 
> > change occurs. the format remains integral.  It would appear that if the 
> > first "day" figure is less than 12 excel transposes the date but if that 
> > "day" figure is more than twelve it doesn't.  I am aware of a similar issue 
> > with merging dates to excel to word but have not yet come across this within 
> > excel itself.  Does anyone have a possible suggestion of a way around this?
> 
> Sure. Instead of just assigning the text of the text box to the cell (letting 
> Excel do any conversions), explicitly convert it yourself:
> 
> Instead of:
>  Range("A1").Value = TextBox1.Text
> 
> Use:
>  Range("A1").Value = CLng(TextBox1.Text)
> 
> For (much) more information about Excel's international issues, see 
> http://www.oaltd.co.uk/ExcelProgRef/Ch22/
> 
> Regards
> 
> Stephen Bullen
> Microsoft MVP - Excel
> www.oaltd.co.uk
> 
> 
> 
0
AndyT (25)
11/3/2004 11:47:01 PM
Hi Andy,

> Cells (NextRow ,1) = TextBox1.Text
> 
> If I change the =TextBox1.Text to =CLng(TextBox1.Text) I get a Runtime Error 
> 13. Forgive me but my knowledge here is scant, perhaps you could let me know 
> where I am going wrong.

Oops!  Sorry, you need a CDate() in the middle there too, to convert the text 
box to a Date number:

If IsDate(TextBox1.Text) Then
  Cells (NextRow ,1) = Clng(CDate(TextBox1.Text))
End If

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk


0
11/5/2004 8:55:55 AM
Many, many thanks Stephen,

You have been a great help.

Kind Regards

"Stephen Bullen" wrote:

> Hi Andy,
> 
> > Cells (NextRow ,1) = TextBox1.Text
> > 
> > If I change the =TextBox1.Text to =CLng(TextBox1.Text) I get a Runtime Error 
> > 13. Forgive me but my knowledge here is scant, perhaps you could let me know 
> > where I am going wrong.
> 
> Oops!  Sorry, you need a CDate() in the middle there too, to convert the text 
> box to a Date number:
> 
> If IsDate(TextBox1.Text) Then
>   Cells (NextRow ,1) = Clng(CDate(TextBox1.Text))
> End If
> 
> Regards
> 
> Stephen Bullen
> Microsoft MVP - Excel
> www.oaltd.co.uk
> 
> 
> 
0
AndyT (25)
11/5/2004 5:08:03 PM
Reply:

Similar Artilces: