July 8, 2021

Validate Time Entry in TextBox on UserForm

How to validate time entry in a textbox on a userform automatically using VBA. Watch the training video below:

Validate Time Entry in TextBox on UserForm automatically using VBA

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

How to validate time entry in a textbox on a user-form automatically
How to validate time entry in a textbox on a user-form automatically

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.