How to restrict some key or Characters in text box in a Excel Macro
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.
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
My name is Vishwamitra Mishra. Friends Call me Vishwa. This blog is authored by me. I am an Excel Geek. Well, this blog talks a lot about my passion in Excels & Macros so I’ll not talk about it :) I am very much passionate about traveling & quite recently discovered that I am a good photographer too..:P
AND GET A FREE!
E-BOOK FOR EXCEL VBA BEGINNERS
DON'T MISS ANY NEW ARTICLE !
Get your FREE! E-Book & Have Every New Article Delivered Straight To Your Email-Box