Date appears in US format when recalled to a User Form.

[Denny57]

I use the following code to automatically apply today's date into a cell in a worksheet when a record is added or modified using a User Form

Private Sub Worksheet_Change(ByVal Target As Range)
' Auto Date
Dim Cell As Range
For Each Cell In Target
If Cell.Column = Range("A:A").Column Then
If Cell.Value <> "" Then
Cells(Cell.Row, "L").Value = Date
Else
Cells(Cell.Row, "L").Value = ""
End If
End If
Next Cell
End Sub

The cells in Column L are custom formatted to dd/mm/yyyy format however, when a record is recalled to the User Form, this date appears in mm/dd/yyyy format. I assume that there must be a way to format the appropriate text box in the VBA code, so any help with the coding of this would be appreciated.

I have checked the default date format in Windows 11 and this is set to dd/mm/yyyy.

 

[dmt32]

The line of code that reads the cell that returns the date to your textbox change the value property of the cell to read Text

Me.TextBox1.Value = Cells(1, 10).Text

This should return exactly what you see in the cell & not it's underlying value.

Dave

 

[Denny57]

The line of code that reads the cell that returns the date to your textbox change the value property of the cell to read Text

Me.TextBox1.Value = Cells(1, 10).Text

This should return exactly what you see in the cell & not it's underlying value.

Dave

Thank you for this. Strangely the problem appears to have been resolved, maybe by coincidence.

I selected File...Advanced.. and then checked and unchecked "Use 1904 date format".

Should the problem return then I will use the VBA code suggested,