Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Key Press Event for Input Box

Status
Not open for further replies.

Jewel142

ISP
Jun 17, 2009
39
Hi Everyone,

Is there a way to limit the types of characters in a user input box (not a text box).

I have a text box that I use for capturing social security numbers and have a key press event set up so that they can only enter numbers. However, if they enter less than 9 characters or skip over the field, I have a Loop set up

Do
SSN = InputBox("Enter a 9-digit social security number (no
dashes)", "Soc Sec Number")
Loop Until SSN.Length = 9
ssnTextBox.Text = SSN

My problem is if they enters an alpha character, my program accepts the alpha character and incorporates it into my string format so that the social looks something like this 123-4t-67o9

Any suggestions?

Thanks!

Jewel
 
You could add in a test to make sure the value is numeric (use the IsNumeric() function). However, that will still allow periods, commas, and the letter "e" (valid if the number is in scientific notation). A more bulletproof solution would be to test the input against a pattern. One way of doing this is with regular expressions. Here is a simple example:
Code:
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
    With RE
        .MultiLine = False
        .Global = False
        .IgnoreCase = True
        .Pattern = "\d{9}"
    End With
Do
  SSN = InputBox("Enter a 9-digit social security number (no dashes)", "Soc Sec Number")
Loop Until RE.test(SSN) And Len(SSN) = 9

The above code was written and tested in Excel 2007, it should work in other versions as well. It should be noted that the code above does not behave as expected if 'cancel' is pressed. There is no code to break out of the loop in that situation.

If you want to get fancy with regular expressions, you could even devise a pattern that would allow the user to enter the dashes in the SSN and strip them out before the value is used in your process.
 
Simply replace this:
Loop Until SSN.Length = 9
with this:
Loop Until SSN Like "#########"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top