Userform Commandbutton Code (Exit) Not Triggering Because Textbox Error Checking Prevents It

[Ark68]

Here is my userform initialization code:

Private Sub UserForm_Initialize()
    Call SystemButtonSettings(Me, False)
    mbevents = True
    Me.cb_wc_sc.Value = False
    Me.cb_wc_wp.Value = False
    Me.empl_surname.Value = ""
    Me.empl_given.Value = ""
    Me.empl_number.Value = "00000"
    Me.empl_shift.Value = "[1]  7:00A - 3:00P"
    cb_tally = 0
    txt_tally = 0
    all_tally = 0
    Me.EMPL_SUBMIT.Enabled = False
    Me.empl_given.Enabled = False
    Me.empl_number.Enabled = False
    Me.empl_shift.Enabled = False
    Me.cb_wc_sc.Locked = True
    Me.cb_wc_wp.Locked = True
    Me.cb_wc_fh.Locked = True
End Sub

When the form is launched, controls are set as above. There is a second commandbutton called "EMPL_EXIT" that when clicked exits and closes the userform. On launch, the cursor is blinking waiting for entry into me.empl_surname, the only textbox accessible. (the other 3 are disabled).

Here is my me.empl_surname exit code:

Private Sub empl_surname_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim strData As String
    'Stop
'invalid entry
    'null
    If empl_surname.Value = "" Then
        MsgBox "Enter the employee's surname. This cannot be left empty.", vbExclamation, "Invalid Entry [SURNAME]"
        empl_surname = ""
        Cancel = True
        Exit Sub
    End If
    If Len(empl_surname.Value) < 2 Then
        MsgBox "Enter the employee's surname." & Chr(13) & "(Min. 2 letters)", vbExclamation, "Invalid Entry [SURNAME]"
        empl_surname = ""
        Cancel = True
        Exit Sub
    End If
    strData = empl_surname.Value
    If HasNumber(strData) = True Then
        MsgBox "Enter the employee's surname without numbers.", vbExclamation, "Invalid Entry [SURNAME]"
        empl_surname = ""
        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
    Me.empl_given.Enabled = True
End Sub

The problem I am here looking for help with is that at this point after the form is launched (no entry into me.empl_surname), I cannot click the EXIT button to leave the userform. Evertime I try, the me.emply_surname exit triggers and I get "Enter the employee's surname. This cannot be empty.). It becomes an endless cycle.

The only way I can use the EXIT button at this stage is to enter a value in me.empl_surname, then the EXIT button works.

My exit button:

Private Sub EMPL_EXIT_Click()
    Stop
    Application.ScreenUpdating = False
    mbevents = False
    With ws_gui
        .Unprotect
        .Range("M2").Value = "- Select operator -"
        .Protect
    End With
    mbevents = True
    Application.ScreenUpdating = True
    Unload Me
End Sub

 

[dmt32]

Hi,

when you click your exit button you move the focus away from your textbox which triggers the exit event hence your issue.

Rather than test each control individually at time of entry, consider testing all controls for completeness when user presses submit button. This can be encompassed in a single function something like this

Private Sub EMPL_SUBMIT_Click()
    If Not AllComplete Then Exit Sub
    
    'rest of code
End Sub

Function AllComplete() As Boolean
    'validation checks for each required control
   
    'etc
    
    
    AllComplete = True
End Function

You could include changing the backcolor of each control to say red that requires users attention.



Dave

 

[Ark68]

Thank you Dave. I will have to look at this more closely. Some of the other controls on my worksheet rely on the appropriate entries of previous fields. So, I'm not sure if waiting until the end to check all controls will work, as some controls will be wrong because their supporting data is wrong.

 

[dmt32]

Thank you Dave. I will have to look at this more closely. Some of the other controls on my worksheet rely on the appropriate entries of previous fields. So, I'm not sure if waiting until the end to check all controls will work, as some controls will be wrong because their supporting data is wrong.

In general. I personally would avoid using Exit event to validate user entry of data that prevents them exiting the control - If you have controls that need to be completed in sequence, you could use the change event to enable the next control in sequence when text is entered - once all controls completed, validate entries for correct data type entry when submit button pressed.

Dave

 

[Ark68]

Thanks Dave, all great advice. I think I originally was using change (or AfterUpdate), but went with exit for the ability to benefit from CANCEL to return a previous default value.
I'm finding there is no "one size fits all" solution.