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?

It doesn't like adding that validation to an empty cell. Check for that
before, if empty put something in and remove it
after:WasEmpty = False
If IsEmpty(Selection) Then
Selection.Value = "z"
WasEmpty = True
End If
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" & 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
If WasEmpty Then Selection.Value = ""


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=131471

On Thu, 3 Sep 2009 23:50:03 +0100, p45cal wrote:

It doesn't like adding that validation to an empty cell. Check for that
before, if empty put something in and remove it
after:WasEmpty = False
If IsEmpty(Selection) Then
Selection.Value = "z"
WasEmpty = True
End If
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" & 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
If WasEmpty Then Selection.Value = ""

Thanks for your reply. Unfortunately, it didn't work. It still gives me the
error message even when a valid ID is there.

Any other ideas?

The next thing to check is that the Selection corresponds to the cell
validation formula.

Another way to make sure that the cell formula refers to the selected
cell is to adjust the *.Add* line to:
Code:
--------------------
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=AND(len(" & Selection.Address(0, 0) & ")=7,ISNUMBER(MID(" & Selection.Address(0, 0) & ",2,6)*1),CODE(LEFT(UPPER(" & Selection.Address(0, 0) & "),1))64,CODE(LEFT(UPPER(" & Selection.Address(0, 0) & "),1))<91)"

--------------------


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=131471

On Fri, 4 Sep 2009 17:20:15 +0100, p45cal wrote:

The next thing to check is that the Selection corresponds to the cell
validation formula.

Another way to make sure that the cell formula refers to the selected
cell is to adjust the *.Add* line to:
Code:
--------------------
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=AND(len(" & Selection.Address(0, 0) & ")=7,ISNUMBER(MID(" & Selection.Address(0, 0) & ",2,6)*1),CODE(LEFT(UPPER(" & Selection.Address(0, 0) & "),1))64,CODE(LEFT(UPPER(" & Selection.Address(0, 0) & "),1))<91)"

--------------------

Thanks again.

I have checked that from the beginning. The data being validated is in Col
B, in the current row (lCurRow) and the validation criteria is going in Col
D in the current row. The initial current row is 7, so the ID is going in
B7 and the validation criteria are going in D7.

Any other suggestions?

salgud;477373 Wrote:
On Fri, 4 Sep 2009 17:20:15 +0100, p45cal wrote:

The next thing to check is that the Selection corresponds to the
cell
validation formula.

Another way to make sure that the cell formula refers to the
selected
cell is to adjust the *.Add* line to:
Code:
--------------------
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop,
Operator:=xlBetween, Formula1:="=AND(len(" & Selection.Address(0, 0) &
")=7,ISNUMBER(MID(" & Selection.Address(0, 0) &
",2,6)*1),CODE(LEFT(UPPER(" & Selection.Address(0, 0) &
"),1))64,CODE(LEFT(UPPER(" & Selection.Address(0, 0) & "),1))<91)"

--------------------

Thanks again.

I have checked that from the beginning. The data being validated is in
Col
B, in the current row (lCurRow) and the validation criteria is going in
Col
D in the current row. The initial current row is 7, so the ID is going
in
B7 and the validation criteria are going in D7.

Any other suggestions?

I hadn't considered this before, I've always considered that data
validation is to check what's going in to the current cell, the one
having new data put in. Sure it can look at the values of other cells to
help decide if it's to be allowed or not.

But you're saying that as you enter something in D7, if B7 doesn't
satisfy criteria, you're not allowed to change what's in D7?

It's an interesting idea - I'll mull over the
possibilities/ramifications, in the meantime, (a) are you sure that's
what you want to do? and (b) clarify in dead simple terms what you do
want.


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=131471

I pasted your code into a blank workbook, and just added
lcurRow = 1 at the top for testing

I added the value of A123456 to cell B1, then selected it and ran the code.
It ran without errors for me (XL2003 on WinXP)

When you run your code and it crashes, what is the value of lcurRow? Where
is that value assigned?

Best,
Keith



"salgud" wrote:

On Thu, 3 Sep 2009 23:50:03 +0100, p45cal wrote:

It doesn't like adding that validation to an empty cell. Check for that
before, if empty put something in and remove it
after:WasEmpty = False
If IsEmpty(Selection) Then
Selection.Value = "z"
WasEmpty = True
End If
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" & 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
If WasEmpty Then Selection.Value = ""

Thanks for your reply. Unfortunately, it didn't work. It still gives me the
error message even when a valid ID is there.

Any other ideas?