Introduction | Example | Tutorial | Applications | Comments
Introduction - VBA TextBox Numbers Only
Learn how to restrict a VBA UserForm TextBox to numbers only using the VBA KeyPress event and KeyAscii argument. This macro is flexible enough to also allow negative numbers and decimals in your UserForm TextBoxes.
In case you want to grab the code and go, I’m going to give you the answer up front. I’ll explain how to use it in the Tutorial section.
Example - VBA TextBox Numbers Only
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) Select Case KeyAscii Case Asc("0") To Asc("9") Case Asc("-") If InStr(1, Me.TextBox1.Text, "-") > 0 Or Me.TextBox1.SelStart > 0 Then KeyAscii = 0 End If Case Asc(".") If InStr(1, Me.TextBox1.Text, ".") > 0 Then KeyAscii = 0 End If Case Else KeyAscii = 0 End Select End Sub
Tutorial - VBA TextBox Numbers Only
From the Beginning
Now that I’ve given you the solution, I’ll set the stage for how to use it. Let’s say you have an Excel UserForm that looks like this:
It’s a simple UserForm, with a TextBox near the center. The TextBox is named
The problem is, there’s no property of the TextBox that will allow you to restrict the input to numbers only. The user can enter any character they want! For your code to function properly, you need to find a way to manually restrict the textbox to numeric values only. I’ll show you how.
From your Project Explorer Pane, right click your UserForm and click View Code.
Once you do that, simply copy and paste the example macro.
Don't forget to change the name!
If your TextBox isn’t named
Everywhere you see
Features of this Macro
Fundamentally, this macro prevents letters and special characters from being entered into your TextBox, but it does more than that.
Because the KeyPress event has the KeyAscii argument, which tells you which character is pressed, you can make some pretty powerful character validation procedures. By reading the KeyAscii argument, this macro is able to include a few features that many other TextBox restriction macros do not.
For example, by checking the KeyAscii value this macro lets the user include a dash, but ONLY if the dash is entered as the first character in the TextBox. This allows the user to enter negative numbers.
Likewise, the macro allows the user to enter a decimal, but it only allows one decimal in the TextBox. A string with more than one decimal isn’t a number!
Anytime you see the line
KeyAscii = 0, it’s telling the VBA compiler to not allow the key to be entered into the TextBox. Notice, the only Case in the Select Case logic that doesn’t have a
KeyAscii = 0 restriction, is the use of numbers from 0 to 9. Makes sense doesn’t it?
Note, the logical test
Case Asc("0") To Asc("9") is the same as entering
Case 48 to 57 since 48 to 57 are the corresponding ascii codes for the numbers 0 through 9.
Application Ideas - VBA TextBox Numbers Only
Macros that validate TextBox entries as numeric are ideal when creating something like a UserForm calculator. I use this in my CF Shapes Add-in when I want the customer to define the conditional formatting statepoints for their shapes. Shameless Plug: If you haven’t checked out what the CF Shapes Add-in can do, you should watch this video.
Now’s the time I ask/beg/plead you to subscribe to my email list. Once you subscribe, you’ll get access to a free copy of my Excel Add-In with tons of useful features and you’ll get monthly tutorials where I show you how to do some pretty cool stuff with VBA:)
About Ryan Wells
Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA applications, including PDF VBA - a leading Excel Add-in for exporting Excel Objects, like charts and tables, to PDFs. Discover more of his popular Excel Add-ins, including Mouse To Macro and CF Shapes, at his dedicated Excel Add-ins page.Follow