Good Morning All...Your help is greatly appreciated!

I am in the process of setting up the Data Validation using Custom option to
validate if the right zip code is entered. the user has the option to either
enter 5 or 9 digit zip code with the option to enter either space or "-" .
here is my code

Function isValidZip(zip As String) As Boolean
Dim Punct, TempZIP As String
If InStr(1, zip, " ") 1 Then
Punct = " "
ElseIf InStr(1, zip, "-") 1 Then
Punct = "-"
End If
If InStr(1, zip, Punct) 1 Then
TempZIP = Left(zip, InStr(1, zip, Punct) - 1)
TempZIP = TempZIP & Right(zip, Len(zip) - InStr(1, zip, Punct))
zip = TempZIP
End If
If Len(zip) = 5 Or Len(zip) = 9 Then
isValidZip = True
Else
isValidZip = False
End If
If Not CInt(zip) Then
MsgBox "Invalid Zip code, Please Try again "
End If
End Function

the problem is I want to check if what they entered is a number or not, if
not show error message. As shown below
If Not CInt(TempZIP) Then
MsgBox "Invalid Zip code, Please Try again "
End If
the CInt returns application error or type mismatch
is there other type of function that i can use or create to check if data
enterd is a number and if not then show error message ?

thank you

you could use the Not IsNumeric concept..........

If Not IsNumeric(TempZIP) Then
MsgBox "Invalid Zip code, Please Try again "
End If

if this doesn't work exactly, try searching
the newsgroup for "Not IsNumeric" for help
with this.
susan

Thank you very much this works!

"Susan" wrote:

you could use the Not IsNumeric concept..........

If Not IsNumeric(TempZIP) Then
MsgBox "Invalid Zip code, Please Try again "
End If

if this doesn't work exactly, try searching
the newsgroup for "Not IsNumeric" for help
with this.
susan

You could also use a pattern search ...

say zip is your string...

something like :
if not (zip like "#####" or _
zip like "#####-####") then

msgbox "Invalid format for zip code", vbinformation
' whatever you want to do ...
end if

what you're telling the VBA compiler - look for an exact match "#####" which
is 5 numbers together or "#####-####" which means 5 numbers a '-' character,
and 4 more numbers ... can modify that to meet your criterion...

hope that helps,
Chad

"Student" wrote:

Good Morning All...Your help is greatly appreciated!

I am in the process of setting up the Data Validation using Custom option to
validate if the right zip code is entered. the user has the option to either
enter 5 or 9 digit zip code with the option to enter either space or "-" .
here is my code

Function isValidZip(zip As String) As Boolean
Dim Punct, TempZIP As String
If InStr(1, zip, " ") 1 Then
Punct = " "
ElseIf InStr(1, zip, "-") 1 Then
Punct = "-"
End If
If InStr(1, zip, Punct) 1 Then
TempZIP = Left(zip, InStr(1, zip, Punct) - 1)
TempZIP = TempZIP & Right(zip, Len(zip) - InStr(1, zip, Punct))
zip = TempZIP
End If
If Len(zip) = 5 Or Len(zip) = 9 Then
isValidZip = True
Else
isValidZip = False
End If
If Not CInt(zip) Then
MsgBox "Invalid Zip code, Please Try again "
End If
End Function

the problem is I want to check if what they entered is a number or not, if
not show error message. As shown below
If Not CInt(TempZIP) Then
MsgBox "Invalid Zip code, Please Try again "
End If
the CInt returns application error or type mismatch
is there other type of function that i can use or create to check if data
enterd is a number and if not then show error message ?

thank you