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

Thanks for your reply.

(snip)

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?

Not quite. If they haven't entered a valid ID in cell B7, then can't enter
a client name in cell D7. They aren't changing anything as the macro is
creating the worksheet before they do any entries. After the sheet is
created, they then enter all the necessary data, like the ID number, name
and various dates and rates of payment, and many of these are validated to
enforce the relevant business rules.

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.
The reason I want to validate the ID no. in D7 rather than B7 is that if I
do it in B7, they can just leave B7 blank, as they often do now, and just
move on and enter the name. I want to prevent them from entering a name if
they don't have a State ID no.

This macro is creating a fairly simple spreadsheet for very unsophisticated
end users. The macro creates the basic formatting, formulas, etc. so that
all they have to do is enter raw data, like ID, name, DOB, dates of
service, and contract rates. The spreadsheet calculates the final payment
nos. based on that data. The validation is there because they have a very
high turnover rate and the new people don't know the business rules and
just put in whatever they think is appropriate. So I'm building the
business rules into this latest build. Things like they have to have a
valid ID (as far as we can check without access to the actual database), a
DOB that is earlier than the date service started, that the service end
date is greater then the service start date, etc.

Hope this helps clarify what I'm doing. Thanks for asking.

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?

You are very welcome. I've gained so much assistance from this group over the
years, I'm glad when I can help others out.

Unfortunately, I don't have an answer to your final question- I only figured
out that it was necessary to add the value through trial and error. It seems
that I can enter data validation directly through the Excel interface without
a problem, but (at least under an unknown set of conditions) adding the data
validation via VBA requires the valid entry. I wish I had a more informative
answer for you.

Best,
Keith

"salgud" wrote:
I can't believe it. After all these tries, it's finally working! I can't
tell you how much I appreciate your patience and your help.

Can you explain to me why it is necessary in this instance to put a "test"
value in the validated cell (the one the validation criteria applies to,
not the one it is in)? I've done many others like this and this is the
first one where that was necessary. Are there some guidelines as to when
this is needed, or is it just trial and error?

Thanks again!

On Thu, 10 Sep 2009 10:16:11 -0700, ker_01 wrote:

You are very welcome. I've gained so much assistance from this group over the
years, I'm glad when I can help others out.

Unfortunately, I don't have an answer to your final question- I only figured
out that it was necessary to add the value through trial and error. It seems
that I can enter data validation directly through the Excel interface without
a problem, but (at least under an unknown set of conditions) adding the data
validation via VBA requires the valid entry. I wish I had a more informative
answer for you.

Best,
Keith

It's a good thing to know. I never would have tried that on my own.

Thanks again for your help. The spreadsheet is now ready to go to the end
users and I think it'll make everyone's life a little easier because the
person entering the data will be getting feedback if they make errors as
they enter it, rather than a week or more later after we've had time to
review it up here.