User Form multiplication - KeyPress version
0. KeyPress
Syntax: Private Sub object_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
object
Required. A valid objectKeyANSI
Required. An integer value that represents a standard numeric ANSI key code
1. the VBA code

1.1 TextBox.KeyPress event
This version allows digits 0 to 9, and "." decimal place, that is, positive numbers only.
Code 1: Sub
txtNumber1_KeyPress
procedure
' ================================ ' 1. Module declarations Dim number1 As Double, number2 As Double Dim txt1 As Boolean, txt2 As Boolean ' ================================ ' 1.1 KeyPress event Private Sub txtNumber1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) ' Positive numbers only, with "." decimal separator Select Case KeyAscii Case Asc("0") To Asc("9") ' if in range 0 to 9 then retain KeyAscii value - goto End Select Case Asc(".") If InStr(1, txtNumber1.Text, ".") > 0 Then ' count number of periods KeyAscii = 0 ' if 1 already End If ' return Ascii 0 (Null) (for this KeyPress) Case Else KeyAscii = 0 ' any other character: return Ascii 0 (Null) End Select End Sub '
Code 1b: Sub
txtNumber2_KeyPress
procedure
' ' ================================ ' 1.2 KeyPress event Private Sub txtNumber2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) ' Positive numbers only, with "." decimal separator Select Case KeyAscii Case Asc("0") To Asc("9") Case Asc(".") If InStr(1, txtNumber2.Text, ".") > 0 Then KeyAscii = 0 End If Case Else KeyAscii = 0 End Select End Sub '
What about negative numbers? Here is the modification to the Select Case statement (lines 37 to 40 below).
' ' ================================ ' 1.2 KeyPress event Private Sub txtNumber2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) ' Positive numbers only, with "." decimal separator Select Case KeyAscii Case Asc("0") To Asc("9") Case Asc("-") If InStr(1, txtNumber2.Text, "-") > 0 Or txtNumber2.SelStart > 0 Then KeyAscii = 0 End If Case Asc(".") If InStr(1, txtNumber2.Text, ".") > 0 Then KeyAscii = 0 End If Case Else KeyAscii = 0 End Select End Sub '
About the highlighted code:
- Line 37: Case Asc("-"). Ascii values: Asc("0") = 48, Asc("9") = 57, Asc("-") = 45, Asc(".") = 46
- Line 38:
InStr(1, txtNumber2.Text, "-")
counts the number of occurrences of "-" in thetxtNumber2
control, starting at position 1. If the current keypress character is "-" from the Case Asc("-") statement, and the existing count is greater than zero, then a Null is returned. - Line 38:
txtNumber2.SelStart > 0
. The SelStart property determines the starting point of the selected text, or the position of the insertion point as in this situation. Suppose that thetxtNumber2.Text
has value 123, then a minus KeyPress will cause SelStart to return 3 and not add a trailing minus sign
1.2 TextBox.Change event and other code
Although the KeyPress event is able to filter the text input, its value lags one character behind the TextBox.Text
contents. This point should be evident from the discussion of code lines 37 and 38. Therefore, the TextBox Change event is required to write the TextBox.Text
value to the module level variables. See code 2a line 53 and code line 62.
Code 2a: Sub
txtNumber1_Change
and txtNumber2_Change
procedures
' ================================ ' 2.1 Change event Private Sub txtNumber1_Change() number1 = VBA.Val(txtNumber1.Text) If txtNumber1.Text <> "" Then txt1 = True Else txt1 = False Call calculator End Sub ' ================================ ' 2.2 Change event Private Sub txtNumber2_Change() number2 = VBA.Val(txtNumber2.Text) If txtNumber2.Text <> "" Then txt2 = True Else txt2 = False Call calculator End Sub '
Code 2b: Sub
txtNumber1_Change
and txtNumber2_Change
procedures
' ' ================================ ' 3. Calculation engine Private Sub calculator() If txt1 And txt2 Then txtResult.Text = number1 * number2 Else txtResult.Text = "" End If End Sub ' 4. Command buttons ' ================================ ' 4.1 Button Click event Private Sub cmdOK_Click() Unload Me End Sub ' ================================ ' 4.2 Button Click event Private Sub cmdOutput_Click() ActiveCell.Value = txtResult.Text End Sub '
References
- Legg C, (2015), VBA: Understanding ByVal In An ActiveX Textbox KeyPress Event Handler Accessed 16 May 2018
- Microsoft Dev Center, (Last Updated: 6/12/2017), KeyPress Event Accessed 16 May 2018
- Download: xlf-frmMultiplyKP1.xlsm [35 KB ]
- Development platform: Office 365 ProPlus - Excel 2016 MSO (16.0...) 64 bit
- Published: 16th May 2018
- Revised: Friday 24th of February 2023 - 02:38 PM, Pacific Time (PT)