Date format in Excel VBA coding

[dss28]

i have been finding it difficult to manage the date format in program developed by me and being used by many users in my department.
in my program i have asked to enter the date as 14/06/2021.
sometimes the entered date is seen as 14/06/2021 or 06/14/2021 or 14-06-2021 or 06-14-2021
Like for formating numbers in decimals i had used code eg. Format(Userform1.TextBox3, "0.000") to save the number in 3 digits.

Likewise is the following code correct to save a data in the desired format?

Code1:

I am transferring the data to a sheet in cell H15 through userform 2


Sheet1.Range("H15").Value = Format(UserForm2.TextBox3, "dd/mm/yyyy")


Code2:
or should I use the code in Userform _Initialise to assign a format for the cell H15 in the sheet1

eg.

Private Sub UserForm_Initialize()
With Sheet1
Range("H15").NumberFormat = "mm/dd/yyyy"

End With

End Sub

will this work or is there any other better solution.

 

[RoryA]

Typically, you'd allow the users to enter the date in whatever format they normally use, then convert it to a real date value using their regional settings before formatting the cell like this:

With Sheet1.Range("H15")
    .Value = CDate(UserForm2.TextBox3)
    .Numberformat = "dd/mm/yyyy"
End With

 

[dss28]

thanks Sir,
will try using this. One small querry....
Is placing this date format code in userform initialisation is correct method or it is to be placed in the main code either in userform or module

 

[RoryA]

There is no point in putting that in the Userform_Initialize event since the date textbox will be empty at that point.

 

[dss28]

oh ... yes ...
thanks a lot

 

[dss28]

There is no point in putting that in the Userform_Initialize event since the date textbox will be empty at that point.

can I use the same in following types of codes:

.Offset(0, 5).Value = UserForm32.TextBox1
.Offset(0, 5).Value.NumberFormat = "dd/mm/yyyy"

 

[RoryA]

Yes, just remember to use CDate round the textbox value as before.