Number validation
Posted by Bob on October 27, 2000 3:14 PM
I have a dialog that is run and one of the input sections asks for the social security number (3 boxes with dashes in between). I would like to validate these numbers in my macro for the following input:
1. Each section must be a number
2. Section 1 must be 3 numbers, Section 2 must be 2 numbers and section 3 must be 4 numbers
What commands do I use and how to accomplish this? I know about the IsNumber function but I need to make sure each has the proper number of digits as well. Thanks for any help I can get.
Posted by Ivan Moala on October 28, 2000 4:03 AM
Assuming Xl97+
and textbox as Msform ie. Userform then
look @ using then KeyPress event eg
Dim TLen As Integer
Const Numbers As String = "1234567890"
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
TLen = TextBox1.TextLength + 1
If InStr(Numbers, Chr(KeyAscii)) = 0 Then KeyAscii = 0
If TLen > 3 Then KeyAscii = 0
End Sub
As this is just for 1 textbox you would have to
change it for the diff criteria (lenght) for the
others.......or do an all in one routine.
HTH
Ivan
Posted by Ivan Moala on October 28, 2000 5:13 AM
By All in one I really meant a function to handle
the diff Lenghts etc....eg assuming textboxs = 3
and name by default textbox1 - 3 then;
Option Explicit
Dim TLen As Integer
Const Numbers As String = "1234567890"
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If TextBoxKey("TextBox1", KeyAscii, 3) = 0 Then KeyAscii = 0
End Sub
Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If TextBoxKey("TextBox2", KeyAscii, 4) = 0 Then KeyAscii = 0
End Sub
Private Sub TextBox3_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If TextBoxKey("TextBox3", KeyAscii, 5) = 0 Then KeyAscii = 0
End Sub
Private Function TextBoxKey(TBox As String, Key, StdLen As Integer) As Integer
Select Case TBox
Case "TextBox1"
TLen = TextBox1.TextLength + 1
Case "TextBox2"
TLen = TextBox2.TextLength + 1
Case "TextBox3"
TLen = TextBox3.TextLength + 1
End Select
'Test if valid number
If InStr(Numbers, Chr(Key)) = 0 Then
TextBoxKey = 0
Else
'If it is then return Ascii value
TextBoxKey = Key
End If
'Test if length is within the limits
If TLen > StdLen Then
'If it isn't then
TextBoxKey = 0
End If
End Function
Ivan
