Pearson Software Consulting Services

    Restricting Entry In Text Box 

         If you are using a VBA Userform, you are almost surely using the TextBox control, and in many of these circumstances you may wish to restrict what sort of data the user is allowed to enter, such as numeric only. Unfortunately, there is no built-in property to restrict a text box to numeric only. However, you can use some simple code in the KeyPress event procedure of the TextBox object to test which character was entered, and allow only numeric (and supporting) characters.

For example, suppose your textbox is named TextBox1. The following code will allow the digits 0-9, a period (.) and a negative sign. The code also limits the user to including a single decimal point, and restricts the minus sign to the first character.

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

To allow other characters, list them in the first Case statement.

See  the Suppressing Change Events In UserForm Controls page for information about suppressing event procedures of controls on user forms.

 

 

 

 

Created By Chip Pearson and Pearson Software Consulting, LLC 
This Page:                Updated: November 06, 2013     
MAIN PAGE    About This Site    Consulting    Downloads  
Page Index     Search    Topic Index    What's New    Links   Legalese And Disclaimers
chip@cpearson.com

© Copyright 1997-2007  Charles H. Pearson