Programming Validation

[Dave]

Hi,
I used the macro recorder to record the setting up of data validation in
certain cells.
It came up with the following code.

Range("C2:C200").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=INDIRECT(A2&B2)"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = False
.ShowError = False
End With

During the recording process, when I entered the validation formula, I got a
message saying:
The source currently evaluates to an error. Do you wish to continue?
So I clicked 'Yes' and continued.
When I run the macro, it stops at the line:
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=INDIRECT(A2&B2)"
and I get an error message:

Run-time error 1004
Application-defined or object-defined error

Is there any way around this? I want the data validation even though it
currently evaluates to an error.

Regards - Dave.

 

[OssieMac]

Hi Dave,

I am assuming the $A$2 and $B$2 point to the start and end of a list range.
If so then I think that it should look something like this. You need the
indirect on both cells.

..Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=INDIRECT($A$2):INDIRECT($B$2)"

 

[OssieMac]

It doesn't need to be absolute with the $ signs the way I posted it. It can
be like the following also.

..Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=INDIRECT(A2):INDIRECT(B2)"

 

[Dave]

Hi,
The concatenation of A1 and A2 together form the name of a named range.
I've been using the Contextures dependent lists ideas.
The =INDIRECT(A2&B2) works ok when I load it manually. I just can't get a
macro to load it.
Regards - Dave.

 

[OssieMac]

Hi Dave,

I am assuming that what you mean is that you have a named range like
MyRngOne. Cell A2 has MyRng and B2 has One. Is that correct?

Took me a while to work it out but it appears that even though you have text
in the cells you need to tell the formula so.

..Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=INDIRECT(TEXT(A2,""@"") & TEXT(B2,""@""))"


Note: when entering the formula in the validation dialog box it looks like
this. Recording adds the additional double quotes that are needed for the
code.

=INDIRECT(TEXT(A2,"@") & TEXT(B2,"@"))

 

[OssieMac]

Hi again Dave,

I did some more work on this because I just can't let a challenge pass.

The following is the results of my testing.

Simply using the concatenate function in lieu of just using the ambersand
works.

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=INDIRECT(CONCATENATE(A2,B2))"


This got me thinking a bit more because it appeared to work when there were
two nested formulas in the brackets so I tried an additional set of brackets
around the concatenation as per the following and it worked also.

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=INDIRECT((A2&B2))"

That got me thinking even more and I decided to include the second parameter
in the INDIRECT function like the following and it also worked fine.

..Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=INDIRECT(A5&B5,TRUE)"

I am now assuming that the INDIRECT function is confusing the second cell
address with the second parameter for the INDIRECT function. When the
concatenated cells are enclosed in brackets either on their own or to use
another function then they are excluded as a second parameter for the
INDIRECT function because the INDIRECT function would see it as only one
value.

I think the last option above is the correct answer.

 

[Dave]

Hi,
I think I haven't explained it properly.
If you look at this link, I'll be able to explain it better.
http://www.contextures.com/xlDataVal02.html
In point 2 of the "Apply the Data Validation" section you will see the
following:
"Note: If cell A2 is empty, you'll see the message shown at right."
"Click Yes to continue"
When doing this manually, the question is asked, I click 'Yes' and the thing
works.
However, If I follow all the steps using that macro recorder, then try to
run the recorded macro, it errors at the point where the message box should
appear.
Regards - Dave.

 

[OssieMac]

Hi again Dave,

Yes. I thought that it was the concatenation that you were having problems
with. Now I see that your real problem is with the error in setting the the
validation. Works OK when in the interactive mode but in VBA the error causes
the code to halt and does not set the validation. I can only come up with a
work around that is to set an if statement in the validation like this.

=IF(B2<>"",INDIRECT(B2),A1:A10)

A1:A10 is a dummy blank range and therefore you don't see any options with
the drop down at cell C2 if B2 is blank. However, when a selection is made in
B2 then the C2 dropdown reflects the named range. Using the above gets around
the error dialog box and therefore allows the recorded VBA code to work.

Don't think that I can come up with anything better.

 

[Dave]

Hi OM,
Yes, that's it! Thanks for your perseverance. As you say, the VBA can't
handle the error message, so we have to prevent it in the first place. Wish
I'd thought of it. Never mind. Always learning.
Regards - Dave.