Kristen_cancerkiller
Member
I have a userform for staff to enter a time value. I want to validate that the time value is in the format of "h:mm am/pm" and if not, I want an error message to show telling them to correct it. Then, I want the active userform field to return to the time field. My current code allows anything to be entered into the field and does not show the error message, nor does it require the field to be corrected. I'm sure it is something minor but I can't figure out what I'm missing. I hope someone here can help me! (As you can see, I have the value moving to my database field in a 'military' format at the end of my code which is what I want there.)
Code:
Private Sub txtDepartTime_AfterUpdate()
'When time is entered, time transfers immediately to spreadsheet datafield.
Dim TargetRow As Long
Dim TestTime As Date
On Error Resume Next
TestTime = TimeValue(txtDepartTime)
On Error GoTo 0
If TestTime = 0 Then
MsgBox "Time entered is not valid. Please enter time as hh:mm am/pm.", vbExclamation
Exit Sub
End If
TargetRow = Sheets("Codes").Range("D43").Value + 1
With Sheets("Travel Expense Voucher").Range("Data_Start").Offset(TargetRow, 25)
.Value = TimeValue(txtDepartTime)
.NumberFormat = "hh:mm" 'departure time
End With
End Sub
Last edited: