Date format switching to mm/dd/yyyy

[Mr_Doug]

Please help. Going insane.

I have a simple sheet with four columns (Date, Details, Reference & Value). I have a textbox on a user form to record a date, but when I submit that value to a cell it is converting it to US format.

I have a BeforeUpdate event on the textbox that coverts that value entered to date. This seems to work fine.

Private Sub tbDateInfo_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
On Error Resume Next
' Me.tbDateInfo = CDate(Me.tbDateInfo)
Me.tbDateInfo = DateValue(Me.tbDateInfo)
End Sub

However, when running to code below, that date is swapped from UK to US format. My regional settings are correct, and the cell format is dd/mm/yyyy.

Private Sub SubmitEntry_Click()

Dim NextEmptyRow As Long
Sheets("Cash").Activate

NextEmptyRow = Application.WorksheetFunction. _
CountA(Range("A:A")) + 1

Cells(NextEmptyRow, 1) = tbDateInfo.Value
Cells(NextEmptyRow, 1).Select
Selection.NumberFormat = "dd/MM/yyyy;@"

Cells(NextEmptyRow, 2) = DetailsInfo.Value
Cells(NextEmptyRow, 3) = RefInfoCombi.Value
Cells(NextEmptyRow, 4) = ValueInfo.Value

End Sub

I would imagine this has been asked before but I was unable to find a solution.

Thanks in advance.

Doug

 

[RoryA]

You should be using CDate when assigning the value to the cell:

Cells(NextEmptyRow, 1) = CDate(tbDateInfo.Value)

 

[Mr_Doug]

Thanks Rory.... That has worked.

Is there anyway to format the CDate on the BeforeUpdate event? This always converts to US format.

 

[RoryA]

Why do you need to format it there?

 

[Mr_Doug]

Aesthetics I guess. If I enter 10/2 (10th Feb) when I tab away it converts to 2/10 (2nd Oct). When I submit the form it works (with your code suggestion) so the end result is OK it just might be confusing for some of the end users.

Thanks for your help, really appreciated.

 

[RoryA]

If you remove the BeforeUpdate code, it will just leave the entry as it is, so what I'm asking is why you need to format it there at all? CDate will use whatever your regional settings are to convert the text input to a true date value.

 

[Mr_Doug]

Hi Rory, thanks for getting back to me again. That's what I've done so all working OK now.

Thanks for the original code change suggestion.

Doug