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.