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
Wikipedia about ASCII: The American Standard Code for Information Interchange (ASCII, pronunciation: /’æski/ ASS-kee;) 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
KeyAscii = 0
MsgBox “Invaild key pressed”
‘ Remark: MsgBox “Non Numerical Key Pressed!”
Watch the Excel training video to see how this interesting solution of entering only numerical values in a user-form text-box is implemented: