How to Allow Only Numerical Values in Excel User Form Text Box Using VBA

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:


2 thoughts on “How to Allow Only Numerical Values in Excel User Form Text Box Using VBA

  1. r.munidasa

    how can i get 2 columns data into a user form-one is text other numerical correspondent to the text. when i select text the corresponding. value is sent to a worksheet cell where it does a calculation using index function..

    Reply
  2. Pingback: Enter numerical data in text boxes in userform using class module | Excel VBA Training Videos

Leave a Reply

Your email address will not be published. Required fields are marked *