How can I place a date in dd/mm/yyyy format into an Excel cell with VBA?

I want to fill a cell in a worksheet with the date as string and I want it to be in the dd/mm/yyyy format. I use the following code but it keeps showing as mm/dd/yyyy. I'm not sure what I am doing wrong since I already looked up the answer.

Private Sub CommandButton1_Click() Dim date1 As String date1 = Format(Date, "dd/mm/yyyy") Worksheets("sheet1").Cells.Range("B1") = date1
End Sub

2 Answers

Format before placing a value in the cell:

Private Sub CommandButton1_Click() With Worksheets("sheet1").Range("B1") .NumberFormat = "dd/mm/yyyy" .Value = Date End With
End Sub

Good Day,

Seems like the underlying format to the cell overrides the formatting in your VBA. I just went and added a custom format before running the code. Which I had a better suggestion. You probably don't need the image, but..