Date formatting from user form to worksheet

[Steve Jones]

I am having a problem transferring a date from a userform to a worksheet in
Excel 2003

This is an extract of the code I am using to enter data from the userform to
the worksheet.

Dim irow As Long
Dim ws As Worksheet
Set ws = Worksheets("Wagedata")

' finds first empty row in database

irow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

ws.Cells(irow, 1).Value = Me.Cmbempno.Value
ws.Cells(irow, 2).Value = Me.Txtempname.Value
ws.Cells(irow, 3).Value = Me.TxtFrmcd.Value
ws.Cells(irow, 4).Value = Me.TxtDate.Value ' date not being entered in the
format required


All works as it should except I want the date displayed in DD/MM/YY format
on the worksheet rather than MM/DD/YY.


Thanks

 

[Dave O]

I didn't try to compile this solution, but it should be a matter of
adding a row similar to this one, just before the code assigns the
value to that cell:

ws.Cells(irow, 4).numberformat = "dd/mm/yyyy"

Dave O
Eschew obfuscation

 

[Dave Peterson]

The problem is in the way the user enters the date in the userform textbox and
the date order that the user specifies in his/her windows date settings. (I'm
guessing that the problem occurs when they use the short date format.)

If you use cdate() to convert the textbox string to a real date, excel/vba will
use that user's setting (see help for cdate for more info).

This is from xl2003 VBA's help for cDate:

CDate recognizes date formats according to the locale setting of your system.
The correct order of day, month, and year may not be determined if it is
provided in a format other than one of the recognized date settings. In
addition, a long date format is not recognized if it also contains the
day-of-the-week string.

=====
But if they type something that isn't a date, you could still have trouble.

Maybe something like this will help:

Option Explicit
Sub testme()

Dim myStr As String

'a date or a warning message
Dim myDate As Variant

myStr = "01/02/03"

On Error Resume Next
myDate = CDate(myStr)
If Err.Number <> 0 Then
Err.Clear
myDate = "Not a date"
End If
On Error GoTo 0

With ActiveSheet.Range("A1")
'unambiguous format for testing!
.NumberFormat = "mmmm dd, yyyy"
.Value = myDate
End With

End Sub

Instead of using a textbox to get the date, you may want to remove any potential
problems by using a calendar control.

Ron de Bruin has some notes:
http://www.rondebruin.nl/calendar.htm

Or even 3 different controls (year, month, day) to get the date.

 

[Lynz]

The problem is in the way the user enters the date in the userform
textbox and the date order that the user specifies in his/her windows
date settings. (I'm guessing that the problem occurs when they use the
short date format.)

If you use cdate() to convert the textbox string to a real date,
excel/vba will use that user's setting (see help for cdate for more info).

This is from xl2003 VBA's help for cDate:

CDate recognizes date formats according to the locale setting of your
system. The correct order of day, month, and year may not be determined
if it is provided in a format other than one of the recognized date
settings. In addition, a long date format is not recognized if it also
contains the day-of-the-week string.

=====
But if they type something that isn't a date, you could still have trouble.

Maybe something like this will help:

Option Explicit
Sub testme()

Dim myStr As String

'a date or a warning message
Dim myDate As Variant

myStr = "01/02/03"

On Error Resume Next
myDate = CDate(myStr)
If Err.Number <> 0 Then
Err.Clear
myDate = "Not a date"
End If
On Error GoTo 0

With ActiveSheet.Range("A1")
'unambiguous format for testing!
.NumberFormat = "mmmm dd, yyyy"
.Value = myDate
End With

End Sub

Instead of using a textbox to get the date, you may want to remove any
potential problems by using a calendar control.

Ron de Bruin has some notes:
http://www.rondebruin.nl/calendar.htm

Or even 3 different controls (year, month, day) to get the date.

Rons calendar is good, or
What about Range.value = Format("dd/mm/yy") in your code or smethiing
similar?
Lyn

 

[Dave Peterson]

Nope.

This line of code:
Range.value = Format("dd/mm/yy")

is essentially typing that string into the cell. And as soon as you do that,
then excel looks at the user's short date setting. So if the user likes mdy,
you won't get the correct date.

 

[Lynz]

Nope.

This line of code:
Range.value = Format("dd/mm/yy")

is essentially typing that string into the cell. And as soon as you do
that, then excel looks at the user's short date setting. So if the user
likes mdy, you won't get the correct date.

Right, I guess it only works for me cause that is what my windows
settings are. Thanks
Lynz

 

[Steve Jones]

Thanks all for your answers - I have managed to find a work around solution
which is not perfect but gets the desired result.

Thanks once again