S
salgud
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(UPPER(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?
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(UPPER(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?