Help with date and time formats

[mikeburton]

Hi All, just learning my way through VBA, MAcros and what have you and have a query regarding date and time formats:
In Excel 2016 I have a spreadsheet to log phone call data. Upon opening the Excel file, a UserForm opens to receive the data to be appended to the last free line in the sheet. This works fine BUT, the first two boxes in the form are Date and Time respectively and are auto-filled. However, I am finding that date reverts to US date format and time is AM/PM. What I would like is DD/MM/YYYY and HH:MM 24hr formats.

My code is below:


Private Sub CommandButton1_Click()
Dim ssheet As Worksheet


Set ssheet = ThisWorkbook.Sheets("Call Log")
nr = ssheet.Cells(Rows.Count, 1).End(xlUp).Row + 1
ssheet.Cells(nr, 1) = CDate(Me.TxtDate)
ssheet.Cells(nr, 2) = CDate(Me.TxtTime)
ssheet.Cells(nr, 3) = Me.LBName
ssheet.Cells(nr, 4) = Me.TxtEng
ssheet.Cells(nr, 5) = Me.LBMachine
ssheet.Cells(nr, 6) = Me.TxtIssue
ssheet.Cells(nr, 7) = Me.TxtResol
ssheet.Cells(nr, 8) = Me.CBMod
ssheet.Cells(nr, 9) = Me.CBResol
ssheet.Cells(nr, 10) = Me.CBCallBck
ssheet.Cells(nr, 10) = Me.CBCallBckDone




'Close UserForm.
Unload Me
UserForm2.Show




End Sub

Please, could you advise what I need to add to force TxtDate and TxtTime to in my preferred formats?
form2.PNG

[georgiboy]

Format(Me.TxtDate,”dd/mm/yyyy”)
Format(Me.TxtTime,”hh:mm”)

might do it.

[georgiboy]

Also make sure that your computer language is set to UK English and not US English as there is a big difference. I had an issue with this a long time ago.

[mikeburton]

Thank you. I had checked the regional setting etc and all were fine.
Where about should the above code be placed for best effect?

[georgiboy]

On the part that writes to the sheet, swap CDate for Format.

On the part that auto populated the time and date boxes on the form, maybe it’s in a Userform_Initialise event. You did not show us the code that populated the form.

[mikeburton]

Here is the entire Userform code as I am not sure where to insert the extra code:

-----
Private Sub CommandButton1_Click()
Dim ssheet As Worksheet


Set ssheet = ThisWorkbook.Sheets("Call Log")
nr = ssheet.Cells(Rows.Count, 1).End(xlUp).Row + 1
ssheet.Cells(nr, 1) = CDate(Me.TxtDate)
ssheet.Cells(nr, 2) = CDate(Me.TxtTime)
ssheet.Cells(nr, 3) = Me.LBName
ssheet.Cells(nr, 4) = Me.TxtEng
ssheet.Cells(nr, 5) = Me.LBMachine
ssheet.Cells(nr, 6) = Me.TxtIssue
ssheet.Cells(nr, 7) = Me.TxtResol
ssheet.Cells(nr, 8) = Me.CBMod
ssheet.Cells(nr, 9) = Me.CBResol
ssheet.Cells(nr, 10) = Me.CBCallBck
ssheet.Cells(nr, 10) = Me.CBCallBckDone




'Close UserForm.
Unload Me
UserForm2.Show




End Sub








Private Sub CommandButton2_Click()


'Close UserForm.
Unload Me

End Sub


Private Sub Label6_Click()


End Sub


Private Sub Label7_Click()


End Sub


Private Sub TxtDate_Change()


End Sub


Private Sub UserForm_Initialize()


Me.TxtDate = Date
Me.TxtTime = Time
End Sub


-----

I am sure it is not the tidiest of codes, but as I say I am trying to learn my way around and much of this involves me using ideas from other people.
Many thanks for your support.

[georgiboy]

Instead of

Me.TxtDate = Date

This

Me.TxtDate = Format(Date,”dd/mm/yyyy”)

You should be able to work out the next line.

hope this helps