Excel VBA Check time in text box to [h]: mm

Question

I am developing a custom VBA Excel form and must enter the time in the format [h]: mm. This means that the clock can be unlimited and not return to 0 after 23:59, as the hh: mm format does. I searched the Internet to no avail.

Here is the code I'm using now:

Private Sub Txtbx_TimeAvailable_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If Me.Txtbx_TimeAvailable.Value <> Format(Me.Txtbx_TimeAvailable.Value, "[h]:mm") Then
        MsgBox "Please enter correct time format in hh:mm"
        Cancel = True
        Me.Txtbx_TimeAvailable.Value = vbNullString
    Else
        ThisWorkbook.Sheets(SELECTEDWORKSHEET).Range("C60").Value = Txtbx_TimeAvailable.Text
        Call UpdateDentistMainForm
    End If

End Sub

However, when using this code, if I enter at 25:53, it will convert it to 01:53. I would appreciate any help I could get from this.

Answer 1

You will need to parse the action manually, here is one way:

Var = "59:59"

Dim IsValid As Boolean
Dim tok() As String: tok = Split(Var, ":")

If (UBound(tok) = 1) Then
    '// `like` to prevent lead +/-
    IsValid = tok(0) Like "#*" And IsNumeric(tok(0)) And tok(1) Like "#*" And IsNumeric(tok(1)) And tok(1) < 60
    '// optionally normalize by removing any lead 0
    Var = Val(tok(0)) & ":" & Val(tok(1))
End If

Debug.Print Var, IsValid