When you are using Text Boxes in Excel Macros then there are some instances where you want to restrict some entries in the Text Box. For example: A Textbox where you are entering Amount. Here you may need that you should be able to Enter All numeric characters from 0-9 and also Decimal (.) and Minus Sign(-).
You don’t have any inbuilt functionality in Excel Textbox to restrict it to only Numeric Value. However by writing a piece of Code under KeyPress Event of Textbox, you can achieve this.

Just copy paste the below mentioned code under KeyPress Event of the Text Box.


 i) To Restrict Non-Numeric Value except Decimal(.) and Minus Sign(-), in Text Box in Excel Macro

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
    Case Asc("0") To Asc("9")
    Case Asc("-")
        If InStr(1, TextBox1.Text, "-") > 0 Or 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

 ii) To Restrict All Keys Except Alphabets in Text Box in Excel Macro

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
    Case Asc("a") To Asc("z")
    Case Asc("A") To Asc("Z")
    Case Else
        KeyAscii = 0
End Select
End Sub

 

 

cover3d_0-89071700_1484285537__1_

Join over 10, 000+ Excel VBA Enthusiasts & get this FREE e-Book Now!