0

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.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • `ByVal cancel As MSForms.ReturnBoolean` is like an 'out' parameter for Exit **event handler** (it's not an ordinary sub and handled by application), If you go into the `Sub _Exit(ByVal cancel As MSForms.ReturnBoolean)` and insert the line `Cancel = True` - application will prevent the exit from the textbox - this is the purpose of that parameter. – Vitaliy Prushak Jan 30 '20 at 10:48
  • @VitaliyPrushak So are you saying I can't call it in the way i've tried above? I've put a `cancel = true` line in, and am still getting a type mismatch with the code as is or if I remove the (cancel) from `call txtCurrentValue_exit()` I get an argument not optional error. – Daniel Barrow Jan 30 '20 at 10:55
  • Can you explain what your ovarall goal with these 2 events is? What is the behavior you like to achieve for that textbox? I think you are up the wrong tree somehow. Actually events are special procedures that get triggered automatically (by an event like mouse click, key down, or exit) they are not intended to be called. – Pᴇʜ Jan 30 '20 at 10:56
  • @Pᴇʜ I am trying to handle the situation where the user might either use the keyboard to navigate through the form, or click in the text boxes independently. I also am trying to prevent the user from leaving a textbox without the validation being run on it, hence the handling of keydown and the exit events. – Daniel Barrow Jan 30 '20 at 10:58
  • If you want to stay in the text box, I would suggest a different way. Declare a private variable a the form code level `Private boolStay as Boolean` and instead of `cancel = False` use `boolStay = True`. Then in the second event code use `If boolStay Then Cancel = True` in its first line. – FaneDuru Jan 30 '20 at 10:59
  • @DanielBarrow This event handler will be called as soon as cursor leave a text box where this handler is implemented, e.g. you press Tab button or select another textbox. If you set the value of `Cancel` to True - handler will not allow to leave the text box and will set focus back to it – Vitaliy Prushak Jan 30 '20 at 10:59

1 Answers1

0

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
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • 1
    This I think should be what i'm looking for. I will implement this way of thinking and come back if I need any assistance. Thank you for your time. – Daniel Barrow Jan 30 '20 at 11:12
  • @DanielBarrow have a look at my edited answer I added some helpful notes. – Pᴇʜ Jan 30 '20 at 11:24