Validation not working

[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?

 

[salgud]

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?

 

[salgud]

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:

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]

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.

 

[ker_01]

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]

Thanks for your reply.

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)

Not sure what that would do. I, too, an running XL2003 and WinXP. If you
put the ID in B1, the validation criteria should be in a different cell.
You can see the detailed description of where the ID is (starts in B7 and
continues down column B) and where the forumla is (same row, column D).
Having them both in the same cell would change things, I imagine, but I
don't know enough about Custom Validation to know how much.

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

When I get the error message, lCurRow is 7, as it should be, and the
selected cells depend on the no. of new rows being added to the
spreadsheet, starting with cell D7 going down Col D. But they are the
correct cells (that is, the ones where I want the validation criteria to
be).

 

[ker_01]

My last post in this thread was prior to me understanding that you wanted the
data validation criteria in a different cell than the value being evaluated.
Nonetheless, it still worked fine for me. Did you test it, and if so, what
errors did it give you?

I set lCurRow to 1, placed a value of "A123456" in B1, then selected C1 and
ran your code. It worked as expected; the code ran without errors, and once
complete, cell C1 could only be changed if cell B1 contained an entry that
meets your validation formula.

Since you don't want to have fake values in B1 when you give your worksheet
to your end users, I would suggest putting a value in that cell before you
create the data validation, then remove that value.

Sub Salgud4
Sheet1.range("B" & lCurRow).value = "A123456"
<your code to set the data validation range for cell C1, or whatever column
you want in this row>
Sheet1.range("B" & lCurRow).value = ""
end Sub

 

[ker_01]

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]

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.