Why am I getting a type mismatch error when trying to pass a variable of type boolean into a txtBox_Exit() event?

Question

I am messing around with userforms in Excel and have hit the first obstacle that I can't resolve either with my own offline resources, or by googling it.

I am trying to handle user input to a selection of textboxes. I have a set of events for those textboxes for the keydown event, and was happy with this until I realised that a lot of end users will try to use their mouse to navigate forms, and turning off the mouse click was a somewhat barbaric fix to the problem.

As a result, I have tried to capture the mouse click event using the keycode method of the keydown, but quickly realised that as they were technically leaving the textbox during the click, an event handling the exit of the textbox currently in use would be more elegant.

Here-in lies my problem.

Due to the two events being largely similar, I have attempted to capture the keypress, and if it is one of a set list of keys, I want to call the exit sub's code. The reason for this is simply to streamline my code. I would use a method for the execution, but there are some variations depending on the textbox.

The keypress capture works. However, the SECOND I put in the line

Call txtCurrentValue_Exit(cancel)

I get a type mismatch error with the cancel variable highlighted.

I can not for the life of me get my head around why I cannot parse a boolean value to a msforms.returnsboolean sub. I am aware this could well be due to a fundamental lack of understanding, but if someone could direct me to the correct way that would be a huge help. I am not necessarily looking for an answer, but more the thought process required to get me there.

Code in question below.

Keydown event as it currently stands:

Private Sub txtCurrentValue_KeyDown(ByVal Keycode As MSForms.ReturnInteger, ByVal Shift As Integer)

If Keycode = vbKeyTab Or Keycode = vbKeyReturn Or Keycode = vbKeyUp Or Keycode = vbKeyDown Then
    Dim cancel As Boolean
    cancel = False
    Call txtCurrentValue_Exit(cancel)
End Sub

Exit event as it currently stands:

Private Sub txtCurrentValue_Exit(ByVal cancel As MSForms.ReturnBoolean)

 'Storage variable for later calculations before punctuation added to variable value

    'Checks to see if the textbox is empty after losing focus
    If txtCurrentValue.TextLength > 0 Then

                'If the value is not numeric, throw an error and reset the value
        If IsNumeric(txtCurrentValue) = False Then
            MsgBox ("Please ensure you only enter numeric values into this box.Punctuation such as £ or % signs will be added automatically.")
            txtCurrentValue.Value = ""
        Exit Sub
        End If

        'value confirmed as numeric, store in storage variable for later calculations
        storedCurrentValue = txtCurrentValue.Value

        'If the user has not elected to use decimal places, put a ".00" on the end of the string value for ease of reading. Also put a £ sign in front of the text.
        If InStr(txtCurrentValue, ".") = False Then
            txtCurrentValue.Value = txtCurrentValue + ".00"
        End If
        If InStr(txtCurrentValue, "£") = False Then
                txtCurrentValue.Value = "£" + txtCurrentValue
        End If
        txtBridgeOffer.SetFocus
    End If
End Sub

The variable storedCurrentValue and the method IsNumeric work in isolation.

Answer 1

But for validation you only need the _exit event. No keydown event needed. The exit event is called whenever the user tries to exit the textbox no matter by mouse or keyboard.

If you set Cancel = True in that event that means the exit action of the text box will be canceled and the cursor will stay in that textbox.

Option Explicit

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim ValidInput As Boolean

    'do some validation here
    If TextBox1 = "aa" Then
       ValidInput = True
    End If

    If ValidInput = False Then
        MsgBox "Only 'aa' is a valid input."
        Cancel = True 'this line will cancel the exit of the textbox and the cursor will set back to that box
    End If
End Sub

For example you cannot leave/exit this textbox if it does not contain aa. This is the only event you need.

---

Additional notes:

You were probably looking for something like this:

Option Explicit

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    With TextBox1  '<-- Your textbox name here
        If .TextLength > 0 Then
            If Not IsNumeric(.Value) Then
                MsgBox ("Please ensure you only enter numeric values into this box.Punctuation such as £ or % signs will be added automatically.")
                .Value = ""
                Cancel = True
                Exit Sub
            End If

            If InStr(.Value, ".") = 0 Then
                .Value = .Value & ".00"
            End If

            If InStr(.Value, "£") = 0 Then
                .Value = "£" & .Value
            End If
        End If
    End With
End Sub

Note that the InStr function does not return a boolean True/False but the position of the first find. So you need to test If InStr(.Value, ".") = 0 because 0 means not found.

Also note that if you change the numeric input value eg 12 into £12.00 and then the user goes back to change it to eg £13.00 it will not be accepted as valid because it is not numeric. So you have to allow that too. Have a look how to check patterns with Regular Expressions the can be very handy for validation.

Here is an example for a RegEx Pattern to check if a text looks like £12.00.
The pattern ^£[0-9]+\.[0-9]{2}$ will match all text that …

• ^£ - Begins with £
• [0-9]+ - followed by one or more digits of 0-9
• \. - followed by a dot .
• [0-9]{2}$ - end with 2 digits of 0-9