the custom validation formula is (for cell C6 )

=AND( LEN(C6)=7, ISNUMBER( MID(C6,2,6) *1 ) )

this checks (1) that the entry is 7 characters and (2) that the 2nd thro
last are numeric
-- if any if the last 6 are not numbers then the last 6 cannot be numeric



"salgud" wrote in message
. ..
On Fri, 24 Jul 2009 22:11:59 +0100, Patrick Molloy wrote:

here is a function that you can use

Function ValidEntry(text As String) As Boolean
'input 7 characters chr 1 is A:Z 2-7 are numeric
If Len(text) < 7 Then Exit Function
Select Case Left(text, 1)
Case "a" To "z", "A" To "Z"
Case Else
Exit Function
End Select
If IsNumeric(Mid(text, 2)) Then
ValidEntry = True
End If
End Function

first check is for 7 characters
second check is the first character is a-z or A-Z
last check is that the last 6 characters "isnumeric" which must be true
only
if all of the characters are numbers



"salgud" wrote in message
.. .
On Fri, 24 Jul 2009 11:33:27 -0600, salgud wrote:

I've submitted this before, but still can't get it to run. I'm entering
validation criteria into a spreadsheet, but keep getting an
"Application
or
object not defined" error on the .Add Type line. The validation formula
is
looking for a 7 digit number, the first of which must be an alpha
character, the other 6 must be numbers.

Public Sub DataValidationClientID()
'Validate that Client ID field has a correct entry before allowing
entry
of
Client Last Name
With Selection.Validation
.Delete

.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:=
_
xlBetween, Formula1:="=AND(CODE(UPPER(B" & lCurRow &
"))64,CODE(UPPER(B" & _
lCurRow & "))<91,LEN(B" & lCurRow & ")=7,ISNUMBER(VALUE(RIGHT(B" &
lCurRow & ",6)))" <----------- ERROR
' .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop,
Operator:=
_
' xlBetween,
Formula1:="=AND(CODE(UPPER(B7))64,CODE(UPPER(B7)) <91,LEN(B7)=7,ISNUMBER(VALUE(RIGHT(B7,6))))"


.IgnoreBlank = False
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "Incorrect ClientID"
.InputMessage = ""
.ErrorMessage = "There is no Client ID or an incorrect Client ID. " _
& "Please enter a correct Client ID in Column B before entering a
Client
Name"
.ShowInput = False
.ShowError = True
End With

Does anyone see the problem? Thanks in advance.

I should have mentioned, the variable lCurRow is declared and has the
correct value when the program halts, and the correct cells for
validation
are selected.

Thanks for your reply. I know I can check it using code. In fact, the
final
phase of this program will be to run all these same checks on the
spreadsheet in case they found a way around the validation (order of entry
effects validation). But for the original data entry person, I want to use
data validation so they know when the enter the data whether they're entry
is correct or not.

This seems far more difficult than I expected. Does anyone else have any
ideas why this code doesn't work?