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