I can't give you the technical explanation for why this works, but hopefully
this will help you keep moving forward, and one of the real gurus here can
provide the "why".

Although the formula can be entered directly, and a recorded macro also
seems to continue to work, as soon as that formula string is broken out into
a new line, or a concatenation of statements, it seems to fail for me too. I
kind of suspect it has something to do with your use of the AND statement,
and lumping all of this in Formula1 (vs Formula2) but that's the technical
part I don't understand.

There are probably even more eloquent ways to recreate your formula, I just
used one that came to mind, and it seems to be working. Please give it a try,
and if it doesn't work when tested against your real worksheet post back to
the group.

Range("A1").Select
With Selection.Validation
.Delete
lCurRow = 6
StrTest = "=SUMPRODUCT((CODE(UPPER(B" & lCurRow &
"))64)*1,(CODE(UPPER(B" & lCurRow & "))<91)*1,(LEN(B" & lCurRow &
")=7)*1,(ISNUMBER(VALUE(RIGHT(B" & lCurRow & ",6))))*1)"
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:=StrTest
End With

HTH,
Keith

"salgud" wrote:

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.