How to fix date in VBA Userform - date is entering into sheet in incorrect format

Question

I am setting up a user form and worksheet to track beer batches. The date brewed in the userform is in the correct format (dd/mm/yyyy), but when entered into the sheet is mm/dd/yyy.

Have tried to change the code used on SUBMIT to the form:

ActiveCell.Offset(0, 3).Value = Format(Now(), "dd/mm/yyyy")
     ActiveCell.Offset(0, 3).Value = Format(Me.txtBrewDate.Value, "dd/mm/yyyy")

Also tried to put before update code on the txtBrewDate on the form:

Private Sub txtBrewDate_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    On Error Resume Next
    Me.txtBrewDate = Format(CDate(Me.txtBrewDate), "dd/mm/yyyy")
End Sub

Have tried on Submit this:

ActiveCell.Offset(0, 3).Value = Format(Now(), "dd/mm/yyyy")
     ActiveCell.Offset(0, 3).Value = Format(Me.txtBrewDate.Value, "dd/mm/yyyy")

Originally it looked like the date was going into the sheet as a TEXT. Then i put the cdate in to the before update - and now looks like a date. But the format is still coming up incorrect (today is 5th of August - but it is coming up 08/05/2019)

No matter how you mix the cake batter, it will only take the shape of the container. Shape the container as you would want it and then put the batter. Do what @Mikku suggested. First change the format of the cell and then put your "batter" in it. :) — Siddharth Rout
Also regarding accepting dates in textbox, you may want to see THIS and then finally THIS — Siddharth Rout

Mikku Mikku · Accepted Answer · 2019-08-05T04:19:43

So first you need to change the Cell Format.

Use:

ActiveCell.Offset(0, 3).NumberFormat = "dd/mm/yyyy"

After That you can put your value in it.

 ActiveCell.Offset(0, 3).Value = Format(Me.txtBrewDate.Value, "dd/mm/yyyy")
 'Or
 ActiveCell.Offset(0, 3).Value = Me.txtBrewDate.Value

How to fix date in VBA Userform - date is entering into sheet in incorrect format

2 Answers