Greg,
I think you can greatly simplify your code by using the LIKE operator. E.g.,
Dim AssentNoInput As String
AssentNoInput = "AZ-12345"
If (AssentNoInput Like "[A-Z,a-z][A-Z,a-z]-####") Or _
(AssentNoInput Like "[A-Z,a-z][A-Z,a-z]-#####") Then
Debug.Print "OK"
Else
Debug.Print "Bad code"
End If
This will accept as OK a string that begins with two letters (upper or
lower case) followed by a "-" and then either 4 or 5 numeric characters. Any
other string will fail the test. See "Like Operator" in VBA help for more
info.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)
"Greg Glynn" wrote in message
ups.com...
The other advantage a form gives you, is you can add Data Validation
(not supported natively, but you can write code to do it), so if you
need specific types of data or specific strings, you can restrict
entry.
I have a field in one form that requires an Asset Number in the Format
"AA-9999" or "AA-99999". So I can force data entry to match this
mask:
Private Sub AssetNoInput_Change()
AssetNoInput = UCase(AssetNoInput)
For Count = 1 To Len(AssetNoInput)
Select Case Count
Case 1, 2
If InStr("ABCDEFGHIJKLMNOPQRSTUVWXYZ", Mid(AssetNoInput,
Count, 1)) = 0 Then
AssetNoInput = xAssetNoInput
Beep
End If
Case 3
If Mid(AssetNoInput, Count, 1) < "-" Then
AssetNoInput = xAssetNoInput
Beep
End If
Case 4 To 7
If InStr("0123456789", Mid(AssetNoInput, Count, 1)) = 0 Then
AssetNoInput = xAssetNoInput
Beep
End If
Case 8
If InStr("0123456789 ", Mid(AssetNoInput, Count, 1)) = 0 Then
AssetNoInput = xAssetNoInput
Beep
End If
Case 9
AssetNoInput = xAssetNoInput
Beep
End Select
Next
xAssetNoInput = AssetNoInput
CheckAssetOK
End Sub