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

Leave a Reply

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