5

I am developing a VBA Excel Userform and need to enter a time in the [h]:mm format. This means that the hours can be unlimited and does not cycle back to 0 after 23:59 like the hh:mm format does. I have searched the web to no avail.

Here is the code I'm currently using:

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 25:53 it converts it to 01:53. I'd appreciate any help I could get on this.

Guru_Tiv
  • 73
  • 6
  • Just a thought: Would it be more effective to store the data as another data type rather than as a date? Perhaps not the best thought, but could be a simple solution to working around your current issue. – StormsEdge Apr 19 '16 at 13:01
  • Take a look at VBA"s TIMEVALUE function. – Doug Glancy Apr 19 '16 at 15:41

1 Answers1

5

You will need to parse the validity 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
Alex K.
  • 171,639
  • 30
  • 264
  • 288
  • Would you explain `tok(0) Like "#*"` ?? – Gary's Student Apr 19 '16 at 13:25
  • 1
    isnumeric("+1234") is true, same for "-1234" so the like forces the 1st char to be a digit to disallow "-123:+5667" – Alex K. Apr 19 '16 at 13:26
  • Alex K your solution works beautifully. I was also trying to get the textbox to redisplay the [h]:mm time formatted data but was getting into issues with that as well. For those interested the solution to this was written by tusharm in MrExcel. `TextBox3.Text = Format(ThisWorkbook.Sheets(1).Range("C1").Value, "[h]:mm")` was only returning the digits ":12" `TextBox3.Text = Application.WorksheetFunction.Text(ThisWorkbook.Sheets(1).Range("C1").Value, "[h]:mm")` returned the correct value back into the textbox. This is because "Format function is a VB(A) function that doesn't understand 24+ hour" – Guru_Tiv Apr 28 '16 at 04:34