mikeburton
03-01-2018, 10:42 AM
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?
21723
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?
21723