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

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
Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,
One comment on “Tom’s Tutorials For Excel: Validate TextBoxes for Numbers Only
  1. Rick Rothstein says:

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

Leave a Reply

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