Excel date format changes from userform to worksheet

[MrMode]

I have a user form with a textbox that the user enters a date into.

When the record is 'saved' - copied to the next empty row in the worksheet, the date changes from dd/mm/yyyy to mm/dd/yyyy even though the settings are for English (UK) date settings both in Excel and locally on the machine.

How do I force the worksheet to accept the text string as a date value without it changing it to US?

When I checked the value of the cell after adding a row through the user form:

MsgBox ActiveCell.NumberFormat

It tells me the format is m/d/yyyy

I have tried applying the formatting as the row is added on a button click

    .Offset(RowCount, 7).Value = Format(Me.Targetdate.Value, "dd/mm/yyyy")

and I have tried to format the text box as the userform is initialized

    Targetdate.Value = Format(Targetdate, "dd/mm/yyyy")

Neither seem to force the 'date' to be displayed properly...

 

[PHV]

Why not change the NumberFormat property of the cell ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MrMode]

Fixed it. Sorry for wasting anyone's time.

In the button_click event, before the value in the text box is pasted into the worksheet, the text string needs to be turned into a date

Dim myStr As String
Dim myDate As Variant

myStr = Me.Targetdate.Value
myDate = CDate(myStr)
   
 RowCount = Worksheets("Sheet1").Range("A1").CurrentRegion.Rows.Count
    With Worksheets("Sheet1").Range("A1")
    .Offset(RowCount, 7).Value = myDate