2

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)

Mikku
  • 6,538
  • 3
  • 15
  • 38
Phnom Penh Newbie
  • 21
  • 2
  • Try `ActiveCell.Offset(0, 3).NumberFormat = "dd/mm/yyyy"` – Mikku Aug 05 '19 at 04:02
  • 1
    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 Aug 05 '19 at 04:14
  • Also regarding accepting dates in textbox, you may want to see [THIS](https://stackoverflow.com/questions/12012206/formatting-mm-dd-yyyy-dates-in-textbox-in-vba) and then finally [THIS](https://stackoverflow.com/questions/54650417/how-can-i-create-a-calendar-input-in-vba-excel) – Siddharth Rout Aug 05 '19 at 04:53

2 Answers2

1

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
Mikku
  • 6,538
  • 3
  • 15
  • 38
-1

First, you need to change the Cell Format. After you change the date to month and the month to date in the VBA view code.

ActiveCell.Offset(0, 2).Value = Format(Me.txt_Date.Value, "mm/dd/yyyy")
Sallar Rabiei
  • 630
  • 1
  • 12
  • 33
Sivanesan N
  • 1