Date Formatting

[ChrisM92]

Hi,

I've created a User Form for our compliance team to log their deductions in to try and reduce errors. However I'm having an issue with the date.

I have the below for when the form opens:

Private Sub UserForm_Initialize()
Dim xRg As Range
DateTextBox.Value = Format(Date, "dd/mm/yyyy")
DateTextBox.Locked = True

And then later on when they hit submit, the below moves it from the form onto the backing sheet:

Worksheets("Data").Range("B" & NextRow) = DateTextBox.Value

Somewhere along the way Excel is deciding that the date is American and so 04/11/2020 on the Form, becomes 11th April in the data sheet.

Any idea how to stop this?

 

[Norie]

Use DateValue to convert the value to a 'real' date value, and if required format the cell the value is going in to show the correct date format.

Worksheets("Data").Range("B" & NextRow) = DateValue(DateTextBox.Value)

 

[ChrisM92]

Use DateValue to convert the value to a 'real' date value, and if required format the cell the value is going in to show the correct date format.

Worksheets("Data").Range("B" & NextRow) = DateValue(DateTextBox.Value)

That was embarrassingly easy..

Thank you, that's worked!