VBA TextBox Numbers Only using Keypress KeyAscii

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

Make powerful macros with our free VBA Developer Kit

This is actually pretty neat. If you have trouble understanding or remembering it, our free VBA Developer Kit can help. It’s loaded with VBA shortcuts to help you make your own macros like this one - we’ll send a copy, along with our Big Book of Excel VBA Macros, to your email address below.

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 TextBox1, but you can name it whatever you like.

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 TextBox1, you’ll have to make a few changes.

Everywhere you see TextBox1 in the example macro, you’ll have to change that to the name of your TextBox. If you’re counting, there should be 4 replacements, including the one in the name of the subroutine.

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.

Now’s the time I ask/beg/plead you to subscribe to my free wellsrPRO VBA Training Program using the form below. 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:)