UK - US Dates

[D Willett]

Using the same text control on a user form. The below code populates the control in the correct format:

Code: Select all

Private Sub cmdToday_Click()

Me.txtDate = Format(Now(), "dd/mm/yyyy")

End Sub

When the date in the control is passed over to the "Data" sheet this is fine.
If the user enters the date manually in then the date in the control is passed over to the "Data" sheet in US format.

Code: Select all

Private Sub cmdAddDetails_Click()
Application.ScreenUpdating = False
UnProtectAll
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Data")

'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

'check for a Name number
If Trim(Me.txtDate.Value) = "" Then
  Me.txtDate.SetFocus
  MsgBox "Please complete the form"
  Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtDate.Value
ws.Cells(iRow, 2).Value = Me.txtOrigJobID.Value
ws.Cells(iRow, 3).Value = Me.txtNewJobID.Value
ws.Cells(iRow, 4).Value = Me.cmbJobType.Value
ws.Cells(iRow, 5).Value = Me.cmbWho.Value
ws.Cells(iRow, 6).Value = Me.txtValue.Value

MsgBox "Data added", vbOKOnly + vbInformation, "Data Added"
'clear the data
Me.txtDate.Value = ""
Me.txtOrigJobID.Value = ""
Me.txtNewJobID.Value = ""
Me.cmbJobType.Value = ""
Me.cmbWho.Value = ""
Me.txtValue.Value = ""
'Me.txtDate.SetFocus
ProtectAll
Application.ScreenUpdating = True
Unload Me

End Sub

I tried changing the following line:
ws.Cells(iRow, 1).Value = Me.txtDate.Value
to
ws.Cells(iRow, 1).Value = Format(Me.txtDate.Value, "dd/mmm/yyyy")

But it still gets passed over in US format not UK.
Is there an easy fix for this?

Kind Regards

[Rudi]

I'd just format the cell explicitly...

Code: Select all

ws.Cells(iRow, 1).Value = Me.txtDate.Value
ws.Cells(iRow, 1).NumberFormat = "dd/mm/yyyy"

[D Willett]

Brilliant Rudi.

Thanks for all the support, much appreciated.