Hitting Exit Button Triggers Userform Field Validation Code Inappropriately

[Ark68]

I am having a logic problem. I have a userform (GROUP) in which the user enters a 5 or 6 digit number into textfield "rental_no".

The user enters a value, and on exit (tab, enter, etc) the following code kicks in:

Private Sub rental_no_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    Dim wshgroup As Worksheet
    Dim vlrange As Range
    Dim ty As Double

    Set wshgroup = Worksheets("Group_Data")

    If Not onExit(rental_no) Then
        With rental_no
            .Value = "000000"
            .SelStart = 0
            .SelLength = Len(.Text)
            .SetFocus
        End With
        Cancel = True
    End If

End Sub

The function "onExit" determines the appropriateness of the value entered. The value entered must be 5 or 6 digits in length, numerical, and be greater than 50000. If not, the user is returned to the field to re-enter until a proper value is determined.

Function onExit(tb As MSForms.TextBox) As Boolean
        
    If Len(tb.Text) < 5 Or Len(tb.Text) > 6 Then
        GoTo InCorrect
    ElseIf Not IsNumeric(tb.Text) Then ' And Value <> vbNullString Then
        GoTo InCorrect
    Else
        ty = Val(tb.Text)
        If ty < 50000 Then
            GoTo InCorrect
        Else
            GoTo Correct
        End If
    End If
    
Exit Function

InCorrect:
    onExit = False
    MsgBox "Incorrect value entered. (5 or 6 numeric digits only)"
    'rental_no.Value = "000000"
    Exit Function
    
Correct:
    onExit = True
    Exit Function
End Function

This all works wonderfully.

On my user form, there is an exit button. The exit button is there for the user to abandon the current form, and return to the main application. However, after an inappropriate value is entered by the user and he is returned to the field to re-enter, should the user choose to exit (by clicking the exit button), rather than the userform closing, he is prompted repeatedly to enter a proper value in the userform field.

Private Sub Exit3_Click()
    Application.EnableEvents = True
    Unload Me
End Sub

What do I need to include to ensure that regardless of where the user is in the process of entering information on the form, that hitting exit will actually exit the form rather than triggering the userfield validation code?

 

[Norie]

Add this at the top of the userform module.

Dim boolExit As Boolean

Then change the code in the sub that exits the form to this.

Private Sub Exit3_Click()
    Application.EnableEvents = True
    boolExit = True
    Unload Me
End Sub

Add this at the start of the Exit sub of the rental_no control.

If boolExit = True Then Exit Sub

This will stop the validation code being called when the form closes.

 

[Ark68]

Hi Norie,

Thanks for your reply. Unless I'm misunderstanding your advice, I applied the suggestions but the actions are still failing. When the user clicks exit, he is still being prompted to enter a valid entry in the userform textbox control (rental_no).

Added

Dim boolExit As Boolean

at the top of the userform module. It, I assume, is a line all by itself ie. not part of any sub??

Here is my revised "EXIT" button code:

Private Sub Exit3_Click()
    'Worksheet("Front").Activate
    boolExit = True
    Application.EnableEvents = True
    Unload Me
End Sub

And the revised exit code of the userform control (rental_no) ...

Private Sub rental_no_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    If boolExit = True Then Exit Sub
    Dim wshgroup As Worksheet
    Dim vlrange As Range
    Dim ty As Double

    Set wshgroup = Worksheets("Group_Data")

    If Not onExit(rental_no) Then
        With rental_no
            .Value = "000000"
            .SelStart = 0
            .SelLength = Len(.Text)
            .SetFocus
        End With
        Cancel = True
    End If

End Sub

 

[Ark68]

Hi friends,

I'm still struggling here with trying to find a solution to my problem. Norie's solution seemed like a logical soution.
Anyone see any glaring errors, or anyone able to provide an alternate solution?