Restrict Userform Textbox input to [H]:MM

Question

I have multiple textboxes on multiple userforms that are for time allocations. For simplicity say userform1 & userform2, with textbox1 & textbox2 on each. Userform1 is for user input, which places values into a table and userform2 pulls the values from this table and displays in the relevant textbox. I need to restrict both the input of these boxes and the display to the [H]:mm format where minutes cannot exceed 59 but hours can be 25+ i.e 125:59 but not 4:67

I tried a combination of code from both of these threads as well as others but can't seem to get it to work.

Excel VBA Textbox time validation to [h]:mm

Time format of text box in excel user form

eventually i just tried to manipulate user input with message boxes but this still leaves entries open to error

Sub FormatHHMM(textbox As Object)

Dim timeStr As String

With textbox

'Check if user put in a colon or not
     If InStr(1, .Value, ":", vbTextCompare) = 0 And Len(.Value) > 1 Then

        MsgBox "Please use HH:mm Format"

        textbox.Value = ""
        textbox.SetFocus
    Else
        If Right(.Value, 2) > 60 Then

        MsgBox "Minutes cannot be more than 59"

        textbox.Value = ""
        textbox.SetFocus

        End If

End If
End With


End Sub

this allows users put alpha characters in and even if correctly input when called from the table is shows as a value instead i.e 5.234... instead of 125:59

Are you hoping to store strings or decimals in your table based on these inputs? — Michael Murphy, Apr 24 '19 at 13:12
@MichaelMurphy i'd say strings. The information on the table will later be printed to a word document that requires Hours and minutes. — RappaportXXX, Apr 24 '19 at 13:22
@JvdV I tried ````if not Isnumeric```` but it triggers on the colon and tells me it's not a number. — RappaportXXX, Apr 24 '19 at 13:24
@RappaportXXX, you could first check for the colon, and right after check both the left and right for numeric values? — JvdV, Apr 24 '19 at 13:26

score 2 · Answer 1 · answered Apr 24 '19 at 13:36

How about you split hours and minutes into two seperate input fields on the same inputbox. So the user has to type in hours and in the next field minutes. This way you can check the input for isnumeric and >60 for seconds. I know this is not ideal, but it would be a way to evade the given problems.

Michael Murphy · Accepted Answer · 2019-04-24T16:41:55.477

Have you tried using the Like operator? That allows checking for numeric values in each character-position. I would do it like this:

Function FormatCheck(ByVal strEntered As String)

Dim correctformat As Boolean

If strEntered Like "*#:##" And IsNumeric(Mid(strEntered, 1, InStr(1, strEntered, ":", 1) - 1)) Then
    If Mid(strEntered, InStr(1, strEntered, ":", 1) + 1, 999) <= 59 Then
        correctformat = True
    End If
End If

If Not correctformat Then FormatCheck = "Incorrect format"

End Function

This requires at least one number before the ":"

Edit: Below is a Sub version instead of using a Function. This will pop up a MsgBox like you were using originally. You could probably replace your whole FormatHHMM sub with this without any adverse effect.

Sub FormatCheck(ByVal strEntered As String)

Dim correctformat As Boolean

If strEntered Like "*#:##" And IsNumeric(Mid(strEntered, 1, InStr(1, strEntered, ":", 1) - 1)) Then
    If Mid(strEntered, InStr(1, strEntered, ":", 1) + 1, 999) <= 59 Then
        correctformat = True
    End If
End If

If Not correctformat Then MsgBox "Incorrect format"

End Sub

i'm not great with functions where would i place this? inside my sub or replace the sub altogether? — RappaportXXX, Apr 24 '19 at 15:59
Functions can be really valuable to figure out and I believe you're already partway to understanding how they work given you have `Sub FormatHHMM(textbox As Object)` in your code. I will edit shortly to provide you with the `Sub` version of my method. — Michael Murphy, Apr 24 '19 at 16:36
Sorry for the delay in coming back to this, played around with this and got it to work based off your code. Just in time to be told my project isn't needed anymore but i learned something anyways lol. Thanks. — RappaportXXX, May 10 '19 at 14:05

Error 1004 · Answer 3 · 2019-04-24T14:50:49.447

i think this may be helpful:

Option Explicit

Sub test()

    Dim str As String

    str = TextBox.Value

    'Test string lenght. Maximun lenght number 4
    If Len(str) <> 4 Then
        MsgBox "Enter a valid time. Proper number of digits are 4."
        Exit Sub
    End If

    'Test if string includes only one ":"
    If (Len(str) - Len(Replace(str, ":", ""))) / Len(":") <> 1 Then
        MsgBox "Use only one "":"" to separate time."
        Exit Sub
    End If

    'Test how many digits are before and after ":"
    If InStr(1, str, ":") <> 2 Then
        MsgBox """:"" position should be place 2."
        Exit Sub
    End If

    'Test if number 1,3 & 4 are number
    If IsNumeric(Mid(str, 1, 1)) = False Or IsNumeric(Mid(str, 1, 1)) = False Or IsNumeric(Mid(str, 1, 1)) = False Then
        MsgBox "Enter number in position 1,3 and 4."
        Exit Sub
    End If

   'Test 2 last to digits
    If Right(str, 2) <= 60 Then
        MsgBox "Second limit is 60."
        Exit Sub
    End If


End Sub

score 0 · Answer 4 · answered Apr 24 '19 at 14:59

You could use regular expressions :

Sub inputTimeFormat()
    Dim userInput As String
    Dim strPattern As String
    Dim msgBoxText As String
    Dim regEx As New RegExp
    Dim objRegex As Object

    strPattern = "(^[0-9]+):([0-5])([0-9])$"
    msgBoxText = "Insert time in HH:mm, or hit Cancel to escape"
    Set objRegex = CreateObject("vbscript.regexp")

    With regEx
        .ignorecase = True
        .Pattern = strPattern
        Do
            If userInput <> vbNullString Then msgBoxText = "PLEASE RETRY" & Chr(13) & msgBoxText
            userInput = Application.InputBox(msgBoxText, Default:="17:01")
            If userInput = "False" Then
                MsgBox "User hit cancel, exiting code", vbCritical
                Exit Sub
            End If
        Loop Until .Test(userInput)
    End With

    MsgBox "Format OK"

End Sub

(you need to activate regular expressions : in VBA, "Tools" > "References" > Check the box "Microsoft VBScript Regular Expressions 5.5" > "OK") More details on How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

Restrict Userform Textbox input to [H]:MM

4 Answers4