Tom’s Tutorials For Excel: Validate TextBoxes for Numbers Only

by Tom Urtis 1 Comment ↓

Tom’s Tutorials For Excel: Validate TextBoxes for Numbers Only

Here’s an example of controlling what a user enters into a TextBox on your userform, in this case, allowing only five whole numbers to be entered.

Suppose you are collecting mailing addresses for your customers, and a United States Zip Code must be entered into a TextBox on your form. To keep things simple, this example assumes the Zip Code will only be five numbers long even though the Post Office maintains a “+4” numbering system that nobody uses.

It’s true that ActiveX textboxes have a MaxLength property that can be set at 5, but doing so does not prompt a message to users who will be pounding their keyboard after the fifth character trying to enter more numbers. This example limits the entry to five whole numbers, and prompts the user immediately if an attempt is made to enter a non-digit character.

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If Len(TextBox1.Text) = 5 Then
KeyAscii = 0
MsgBox "You may enter up to five digits.", 48, "That number is too long."
Exit Sub
End If
Select Case KeyAscii
Case 48 To 57
Case Else
KeyAscii = 0
MsgBox "Only whole numbers are allowed.", 48, "Whole numbers only."
End Select
End Sub

‹ Tom’s Tutorials For Excel: Delete a Table in Access From Excel

Tom’s Tutorials For Excel: Does a Worksheet Exist, and Create One if Not? ›

Posted in Tom's Tutorials for Excel
Tags: ActiveX / Form Controls, Analytics, Big Data, Business Intelligence, Data Science, Excel Expert, Excel Guru, Macros Events Classes API, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Numbers and Decimals, Tom Urtis, UserForms

One comment on “Tom’s Tutorials For Excel: Validate TextBoxes for Numbers Only”

1. Rick Rothstein says:

   March 1, 2021 at 3:32 pm

   I see one problem with your code… it does not stop someone from copy/pasting as many digits or non-digits as they want into the TextBox. Here is a set of procedures coupled with a global variable which allows the user to only enter or paste a maximum of 5 digits only into the TextBox. This is a modification of code I developed somewhere between 15 to 20 years ago for the compiled version of Visual Basic.

   ************************** START OF CODE **************************
   Dim LastPosition As Long

   Private Sub TextBox1_Change()
   Static LastText As String
   Static SecondTime As Boolean
   If Not SecondTime Then
   With TextBox1
   If .Text Like “*[!0-9]*” Or Len(.Text) > 5 Then
   MsgBox “A maximum of 5 digits are allowed in this TextBox!”, vbExclamation
   SecondTime = True
   .Text = LastText
   .SelStart = LastPosition
   Else
   LastText = .Text
   End If
   End With
   End If
   SecondTime = False
   End Sub

   Private Sub TextBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
   LastPosition = TextBox1.SelStart
   End Sub

   Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
   LastPosition = TextBox1.SelStart
   End Sub
   ************************** END OF CODE **************************

   Reply