User Form multiplication - KeyPress version


0. KeyPress


Syntax: Private Sub object_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)


1. the VBA code


media/xlf-multiply-kp1
Fig 1: UserForm frmMultiplyKP1 - with KeyPress number validation

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:


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