I have a spreadsheet using Data Validation in which the user must enter a
state ID no into column B. The first entry is in B7, they go down from
there. The spreadsheet is created by a macro which also enters the
Validation Criteria in the Custom Formula box. A valid state ID no consists
of one letter followed by 6 numbers. E.g., A123456.

When I enter the following formula into cell D7

=AND(LEN(B7)=7,ISNUMBER(MID(B7,2,6)*1),CODE(LEFT(U PPER(B7),1))64,CODE(LEFT(UPPER(B7),1))<91)

it works fine to validate that the ID in cell B7 is correct.

Translated into code to create a Custom Data Validation formula, it looks
like this:

With Selection.Validation
.Delete
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=AND(len(B" & lCurRow & ")=7,ISNUMBER(MID(B" &
lCurRow _
& ",2,6)*1),CODE(LEFT(UPPER(B" & lCurRow &
"),1))64,CODE(LEFT(UPPER(B" _ <---- ERROR
& lCurRow & "),1))<91)"
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "Client ID Error"
.InputMessage = ""
.ErrorMessage = "The State ID must consist of 1 letter and 6 numbers
(A123456)"
.ShowInput = False
.ShowError = True
.IgnoreBlank = False
End With

But I get an object defined error on the ".Add Type" line.

Any suggestions?