Visual Basic for Applications/Block Illegal Characters
This VBA example can be run in any of the commonly used Microsoft Office applications. The examples show how to exclude illegal characters from text boxes while the keys are being pressed. In each of the two examples, the insertion point simply remains where it was when an illegal character is entered; accepted characters appear in the usual way. Both examples make use of the TextBox_KeyPress event; then first accepting only integers and a few other characters, and the second only letters and some supporting characters.
The VBA CodeEdit
For the ThisWorkbook ModuleEdit
'............................................... ' Notes: Code needs a user form named UserForm1, ' with two text boxes, TextBox1 and Textbox2, ' and a command button with name CommandButton1. ' Set UserForm1 property ShowModal to False '............................................... Private Sub Workbook_Open() 'Runs on opening the workbook Load UserForm1 UserForm1.Show End Sub
For the UserForm1 ModuleEdit
- Make sure that the ShowModal property of the user form is set to False, to allow normal code working and study with an open form.
- Copy the code below into the UserForm1 module, and type text into the boxes to see the results.
- Setting the parameter KeyAscii in code to the ASCI value of a non-printing character (eg: zero), prevents the display of the character that originated the event. Otherwise, KeyAscii is found to contain the ASCI value of the key that was pressed, and it is displayed.
- The KeyAscii value can be changed to any asci value, and provided that it is a printing character, it will be displayed.
- In each case code is added to restrict the permitted positions in the text. For example, a minus sign is valid only at the start of a number, and a hyphen is never expected at the start of a word.
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) 'Block character input other than integers, minus signs, and periods 'For example, to enter a number and decimal, eg; -4756.09 Select Case KeyAscii 'accept integers Case Asc("0") To Asc("9") Case Asc("-") ' unless one already exists or cursor not at start If InStr(1, Me.TextBox1.Text, "-") > 0 Or Me.TextBox1.SelStart > 0 Then KeyAscii = 0 'return a non-printing character End If Case Asc(".") 'unless one exists already If InStr(1, Me.TextBox1.Text, ".") > 0 Then KeyAscii = 0 'return a non-printing character End If Case Else KeyAscii = 0 'return a non-printing character End Select End Sub Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) 'Block character input other than letters, hyphens, periods, and space ' For example, for title, forename and surname, eg; Mr. John Doe. Select Case KeyAscii 'accept upper and lower case letters Case Asc("a") To Asc("z"), Asc("A") To Asc("Z") Case Asc("-"), Asc("."), Asc(" ") 'ok provided on at least the third character If Me.TextBox2.SelStart < 2 Then 'zero is first KeyAscii = 0 'return a non-printing character End If Case Else KeyAscii = 0 'return a non-printing character End Select End Sub