How to Allow ONLY Numerical Values in Excel user-form Text-Box Using VBA

Home More Excel (VBA) Training Videos

I have created a UserForm for Item Name & Unit Price for easy data entry. I wish to limit the user to enter only numerical values. If she presses any alpha keys a message should be displayed such as "Invalid Entry".
First we create a user-form with the following labels and text-boxes
  • Label1 - Item Name
  • TextBox1 - Here the user will input the name of the item
  • Label2 - Unit Price
  • TextBox2 - Here the user will enter the unit price of the item and we wish to allow only numerical values, a decimal '.' or a space
  • Next we place a command button that will demonstrate how to transfer the data from the user-form to the Excel worksheet.
The macro or VBA coding for the TextBox2 and the command button is given below:
Private Sub CommandButton1_Click()
Range("a2").Value = TextBox1.Text
Range("b2").Value=TextBox2.Text
End Sub


Wikipedia about ASCII: The American Standard Code for Information Interchange (ASCII, pronunciation: /'ęski/ ASS-kee;[1]) is a character-encoding scheme originally based on the English alphabet. ASCII codes represent text in computers, communications equipment, and other devices that use text. Most modern character-encoding schemes are based on ASCII, though they support many additional characters.

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If (KeyAscii > 47 And KeyAscii < 58) Or KeyAscii = 46 Or KeyAscii = 32 Then
' Remark: If (KeyAscii >=48 And KeyAscii <=57) Or KeyAscii = 46 Or KeyAscii = 32 Then 
 KeyAscii = KeyAscii
 Else
 KeyAscii = 0
 MsgBox "Invaild key pressed"
' Remark: MsgBox "Non Numerical Key Pressed!"
 End If
End Sub
Watch the Excel training video to see how this interesting solution of entering only numerical values in a user-form text-box is implemented:




Watch the video on our youtube channel
Home More Excel (VBA) Training Videos