Key Press Event for Input Box

[Jewel142]

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

 

[jges]

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:

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.

 

[Jewel142]

Thanks I'll give that a try!

 

[PHV]

Simply replace this:
Loop Until SSN.Length = 9
with this:
Loop Until SSN Like "#########"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886