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.
On 08/05/2010 09:58, Steve Jones wrote:
> 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 Peterson
Similar Threads
