Two Questions Around Error Trapping A Userform Textbox Entry That is Supposed To Be Numbers Only

[Ark68]

I have this code:

Private Sub empl_number_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Stop
'invalid entry
    If IsError(CLng(empl_given.Value)) Then
        MsgBox "Not a number."
        empl_number = "00000"
        Cancel = True
        Exit Sub
    End If
End Sub

I have a couple questions:

1) This chunk of code is supposed to chech the value of textbox Me.empl_number to ensure the user entered a 5 digit numerical value. My logic is that using Clng to convert the textbox string value to a number would err if there were anything but numbers in the string. I am getting an error with the line in red: "Type mismatch". Nothing is declared, becuase I don't know what to declare.

2)If there is an error (not a number), the code defaults the value in the textbox to "00000". I would like to highlight the this value to allow the user to type over it. Unsure how.

 

[NoSparks]

Try this

Private Sub empl_number_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If Me.empl_number = "" Then Exit Sub
    If Not IsNumeric(Me.empl_number) Or Len(Me.empl_number) <> 5 Then
        MsgBox "Not a number" & vbLf & "OR" & vbLf & _
               "Wrong number of digits."
        Cancel = True
        With Me.empl_number
            .Value = "00000"
            .SelStart = 0
            .SelLength = 5
        End With
    End If
End Sub

 

[Rick Rothstein]

To test for a 5-digit number, use this (replacing my example ValueToCheck variable name with your own variable or reference, of course)...

If ValueToCheck Like "#####" Then

If you want to check if the entry is not a 5-digit number, then use this...

If Not (ValueToCheck Like "#####") Then

 

[Ark68]

Hi folks.
Thank you so much for your help. Both were very helpful and provided the results I needed.
Rick, thank you for sharing a new function, an NoSparks thanks to you for getting me over ther hurdle.

 

[Ark68]

If I can expand on this for a related problem ...

Me.empl_number is disabled until the previous textbox (Me.empl_given) has a valid valid entered. Once the value is entered, Me.empl_number is enabled.
I tried this code hoping that it would highlight the five zeros of the employee number to be overwritten without the user having to click the field. Similar to what happens automatically after an error.

With Me.empl_number
        .Enabled = True
        .Value = "00000"
        .SelStart = 0
        .SelLength = 5
        '.SetFocus
    End With

This does not highlight (select) the default value of 00000, and the user has to to manually highlight it in order to overwrite it, or highlight and delete the default, to enter in their value. Again, hioping to figure out how to highlight to just be able to overwrite it.

 

[NoSparks]

In the original post of empl_number_Exit code you had

     If IsError(CLng(empl_given.Value)) Then

Now you seem to have the same type of 'mistaken identity' in what I would think will be your empl_given_Exit code

With Me.empl_number

 

[Ark68]

Thanks NoSparks, but I have to apologize for not being able to follow. Yes, I made an error in my original post, I discovered that after I had posted and made the correction.

In this case, the scenario is different. The OP was "defaulting" the employee number (00000) after an error.

What I am trying to do with post 5 is prepare textbox me.empl_number for user entry after the user tabs out of (enters data into) the previous textbox me.empl_given. The code in post 5 is part of the empl_given exit event posted in full below.

ie:
User enters data into textbox empl_given
Tab
textbox empl_number becomes enabled, default value populated, default value highlighted (selected) to be overwritten.

This doesn't seem to be happening. The user still needs to click in the empl_number textbox and manually highlight the 00000 to overwrite it.

rivate Sub empl_given_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim strData As String
'invalid entry
    'null
    If empl_given.Value = "" Then
        MsgBox "Enter the employee's given name. This cannot be left empty.", vbExclamation, "Invalid Entry [SURNAME]"
        empl_given = ""
        Cancel = True
        Exit Sub
    End If
    If Len(empl_given.Value) < 2 Then
        MsgBox "Enter the employee's given name." & Chr(13) & "(Min. 2 letters?", vbExclamation, "Invalid Entry [SURNAME]"
        empl_given = ""
        Cancel = True
        Exit Sub
    End If
    strData = empl_given.Value
    If HasNumber(strData) = True Then
        MsgBox "Enter the employee's given name without numbers.", vbExclamation, "Invalid Entry [SURNAME]"
        empl_given = ""
        Cancel = True
        Exit Sub
    End If

'valid entry
'    Stop
    txt_tally = 0
    cb_tally = 0
    For Each ctrl In Me.Controls
        Debug.Print TypeName(ctrl)
        If TypeName(ctrl) = "TextBox" Then
            If ctrl.Value <> "" Then txt_tally = txt_tally + 1
        ElseIf TypeName(ctrl) = "CheckBox" Then
            If ctrl.Value = True Then cb_tally = cb_tally + 1
        End If
    Next ctrl
    If Me.empl_number.Value = "00000" Then txt_tally = txt_tally - 1
    
    all_tally = txt_tally + cb_tally
    If all_tally = 4 Then
        Me.EMPL_SUBMIT.Enabled = True
    End If
    
    'Stop
    'prepare next textbox for user entry - highlight default value for overwriting
    With Me.empl_number
        .Enabled = True
        '.Value = "00000"
        '.SelStart = 0
        '.SelLength = 5
        '.SetFocus
    End With
End Sub

Now, whether related or not, I don't know ... but I have been having a real hard time implementing a tab order. Although I have empl_given set at tab order 2, and empl_number at tab order 3, when the user tabs out of empl_given, it doesn't go to empl_number. It goes to a checkbox that is assigned a tab order of 5.

 

[RoryA]

It's generally a bad idea to try and use any event triggered by leaving a control to set focus to another control. I'd suggest you lock the textbox rather than disabling it.

 

[Ark68]

It's generally a bad idea to try and use any event triggered by leaving a control to set focus to another control. I'd suggest you lock the textbox rather than disabling it.

Rory ... what a difference that made! It solved two problems with a simple change to a few lines.
Thank you!