User Form Macro - Date Format is Changing from UK to US format on spreadsheet

[Lesgrimes]

Hi All,

after receiving a cracking answer to an issue I raised yesterday on the Forum, I now seem to have an issue with around Date Formats

For my user form I have 3 different text boxes for Date entry, so I have set up code for each one to format the Date into UK format

Private Sub TextBoxNewDeliveryDate_AfterUpdate()If IsDate(Me.TextBoxNewDeliveryDate) Then
Me.TextBoxNewDeliveryDate = Format(Me.TextBoxNewDeliveryDate, "dd/mm/yy")
End If
End Sub

This seems to allow me to put the date into the user form correctly.

However, when I then click on the Save button and the code copies the values to the spreadsheet the dates have changed format to US!!! It appears to be quite selective as well, as days which could be seen as months 1 to 12 are changing and all dates past the 13th do not change.

'Copy Input values to sheet.
Dim lRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Non Conformance")
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
With ws
.Cells(lRow, 1).Value = Me.TextBoxDateEntry.Value
.Cells(lRow, 2).Value = Me.ComboBoxShip.Value
.Cells(lRow, 3).Value = Me.ComboBoxCategory.Value
.Cells(lRow, 4).Value = Me.ComboBoxType.Value
.Cells(lRow, 5).Value = Me.TextBoxOrigDelDate.Value
.Cells(lRow, 6).Value = Me.TextBoxOrigDestination.Value
.Cells(lRow, 7).Value = Me.TextBoxPONumber.Value
.Cells(lRow, 8).Value = Me.TextBoxSKUNumber.Value
.Cells(lRow, 10).Value = Me.TextBoxNewDeliveryDate.Value

I've tried formatting the columns on the spreadsheet but this does nothing as I presume the code is overwriting it?

Do I need to add some code to the copy VBA or somewhere else?

Thanks in advance

 

[Fluff]

Try

.Cells(lRow, 1).Value = CLng(Me.TextBoxDateEntry.Value)

 

[Lesgrimes]

Thank you Mr Fluff, excuse my ignorance but I tried replacing the line

.Cells(lRow, 1).Value = Me.TextBoxDateEntry.Value

with your suggested code and it brought up an error message.
I presume with my lack of coding skills I put the new code in the wrong place?

 

[Fluff]

Apologies it should have been

[COLOR=#ff0000]CDate[/COLOR](Me.TextBox1.Value)