Date format issue when submitting from a userform to a spreadsheet

A

Andy T

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
 
S

Stephen Bullen

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?
Click to expand...

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
 
A

Andy T

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
 
S

Stephen Bullen

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.
Click to expand...

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
 
Top