Visual Basic for Applications/Block Illegal Characters

Summary

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 Code

For the ThisWorkbook Module

'...............................................
' 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 Module

• 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