Userform Textbox Time Format Validation

[natekris8183]

I have looked online and found multiple references for validation of Date entry for userform textboxes, but I need something for time entry. I am doing a time study at work, and have created an automated and manual time entry log. The automated time entry is fine as it has a start/stop command button to capture the current time when pressed; however, I need to allow people to manually enter time when not available to use their PC. So I have the same two textboxes that hold the automated time entries then enabled for manual time entry. I need to make sure time is entered as "HH:MM:SS AM/PM" format. Formatting on exit is fine for it, except for when someone inputs "13:" and exits, it doesn't format to "01:00:00 PM" as it should. It stays as "13:". I need one of two solutions, either how to fix this validation so that it cancels out the exit if the entry is not entered as "HH:MM:SS AM/PM" OR when I press CommandButton "Save" to migrate the data into the table, to validate that the values in the StartTime textbox and EndTime textbox, are both entered AS time. Does anyone else wish there was a IsTime function such as IsDate

 

[Worf]

IsDate does it:

Sub IsTime()


    Dim t, w$
    t = "13:":    w = "not"
    If IsDate(t) Then w = " "
    MsgBox "It is " & w & " a time", vbInformation, t
    
    t = "1:00 am":    w = "not"
    If IsDate(t) Then w = ""
    MsgBox "It is" & w & " a time", vbInformation, t
    
End Sub

 

[natekris8183]

I'm not sure how this is intended to work, but I have modified it for the Exit event and it determines all inputs to not be time format, even if input as "HH:MM:SS".

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)[INDENT]Dim t, w$
[/INDENT]
     t = "13:":    w = "not"
    If IsDate(t) Then
        Me.TextBox1.Value = Format(Me.TextBox1.Value, "HH:MM:SS AM/PM")
    Else
        MsgBox "Input time as HH:MM:SS military"
        Me.TextBox1.Value = ""
        Cancel = True
    End If
    
End Sub

 

[Worf]

Try it like this:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If IsDate(Me.TextBox1.Value) Then
        Me.TextBox1.Value = Format(Me.TextBox1.Value, "HH:MM:SS AM/PM")
    Else
        MsgBox "Input time as HH:MM:SS military"
        Me.TextBox1.Value = ""
        Cancel = True
    End If
End Sub

 

[natekris8183]

That's precisely what I did. I don't know why I was making that more stinking complicated than it EVER had to be. Thanks for all the help!

 

[Worf]

You are welcome

 

[vbaBaby]

Is it possible for the user to leave the textbox blank without it returning the validation message box. I'm asking cause in my userform the user can tab through the different textboxes to enter the information and they have the option of leaving it blank. I'm not sure what i need to enter in the code for it to not bring up the message if the box is blank.

 

[Worf]

This version will behave in the following way, according to the input, when tabbing the text box:
- Blank: does nothing
- Date: formats it
- Non date: erases the data

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If IsDate(Me.TextBox1.Value) Then
        Me.TextBox1.Value = Format(Me.TextBox1.Value, "HH:MM:SS AM/PM")
Else
        Me.TextBox1.Value = ""
End If
End Sub