How to validate time entry in a textbox on a userform automatically using VBA. Watch the training video below:
Last time we used a custom time function which helped us to identify a correct time entry or reject an invalid entry. Today we will learn how to validate a time entry in a text box on user-form quickly and easily. Below is the macro VBA code to test whether a time entry is acceptable or invalid:
Private Sub CommandButton1_Click()
On Error GoTo MyTimeEntry_ErrorHandler
Dim MyTextBoxEntry As String
Dim MyHours As Integer
Dim MyMinutes As Integer
Dim MySeconds As Integer
‘time format “HH:MM:SS”
MyTextBoxEntry = TextBox1.Value
‘Check If the 3rd charcter of the time string is a colon character
If Mid(MyTextBoxEntry, 3, 1) <> “:” Then
MsgBox "Invalid time entry!"
TextBox1 = ""
TextBox1.SetFocus
Exit Sub
End If
‘Check If the 6th character of the time string is a colon character
If Mid(MyTextBoxEntry, 6, 1) <> “:” Then
MsgBox "Invalid time entry!"
TextBox1 = ""
TextBox1.SetFocus
Exit Sub
End If
‘we get the numeric values for the hours, minutes and seconds
MyHours = Int(Left(MyTextBoxEntry, 2))
MyMinutes = Int(Mid(MyTextBoxEntry, 4, 2))
MySeconds = Right(MyTextBoxEntry, 2)
‘Check If the hours entry makes sense
If MyHours < 0 Or MyHours > 24 Then
MsgBox "Invalid time entry!"
TextBox1 = ""
TextBox1.SetFocus
Exit Sub
End If
‘Check If the minutes entry makes sense
If MyMinutes < 0 Or MyMinutes > 59 Then
MsgBox "Invalid time entry!"
TextBox1 = ""
TextBox1.SetFocus
Exit Sub
End If
‘Check If the seconds entry makes sense
If MySeconds < 0 Or MySeconds > 59 Then
MsgBox "Invalid time entry!"
TextBox1 = ""
TextBox1.SetFocus
Exit Sub
End If
‘Check If the hours and minutes entries makes sense
If MyHours = 24 And MyMinutes > 0 Then
MsgBox "Invalid time entry!"
TextBox1 = ""
TextBox1.SetFocus
Exit Sub
End If
‘Check If the hours, minutes and seconds entries as a whole makes sense
If MyHours = 24 And MyMinutes = 0 And MySeconds > 0 Then
MsgBox "Invalid time entry!"
TextBox1 = ""
TextBox1.SetFocus
Exit Sub
End If
‘Now the time entry should be a valid entry
Range("A1") = TextBox1.Value
FormatTime
Exit Sub
‘Check If the entry has some other error – return FALSE and EXIT
MyTimeEntry_ErrorHandler:
MsgBox “Invalid time entry!”
TextBox1 = “”
TextBox1.SetFocus
Exit Sub
End Sub
Private Sub UserForm_Initialize()
MsgBox “Please enter time as hh:mm:ss,” & vbCrLf & “for example, 09:30:30 or 15:20:45”
End Sub
