Quick Jump
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

Get wellsrPRO for Free

Get these macros delivered right to your spreadsheet for free

Access tutorials and import my macros without ever leaving Excel

Show me - It's free

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:

UserForm with TextBox

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.

View Code UserForm

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. 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.

Comments

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:)

Share this article with the world on Google+, Twitter and Facebook!


Get wellsrPRO for Free

Get these macros delivered right to your spreadsheet for free

Access tutorials and import my macros without ever leaving Excel

Show me - It's free

About Ryan Wells


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.