Trouble setting validation reference to cells on another worksheet

[daylward]

Hello, my first post here, forgive me if I overlook some convention. I'll try to be as concise and clear as possible.

I have a worksheet I use as an invoice. Some columns in the invoice have data validation to let the user pick values. I have a number of macros, one of which cleans up the sheet to correct issues resulting from cutting/pasting or other careless use of the invoice. Part of that cleanup macro makes sure the data validation of the columns is correct.

The validation list source for one of the columns is on a different worksheet, with this formula:

Please Login or Register  to view this content.

At this point I'm assuming that the validation is currently unmolested and correct in the last cell of the column, and I want to make all of the cells in the column have the same validation. Here's the code I use (starting with "oValidation" being set to the .Validation property of the last cell in the column, and "oCol" being the range representing the entire invoice column in question):

Please Login or Register  to view this content.

Should work, right? Doesn't. I get no errors, but the validation formula does not get set correctly on two levels. As I step through the code and step past the "...Validation.Add" line, ".Formula1" shows "=Parameters!$B$50:$B$58" but "oTempRange.Validation.Formula1" is only "=$B$50:$B$58" (in other words, it drops the sheet reference). Moreover, when I have a cell within that range selected, and type this in the immediate window:

Please Login or Register  to view this content.

I get the following error:

---------------------------
Microsoft Visual Basic for Applications
---------------------------
Run-time error '1004':

Application-defined or object-defined error
---------------------------
OK Help
---------------------------

... which is the error it gives when the range in question does not have validation. Just to be sure, when I select the last cell in the column and then execute the same line in the immediate window, it gives the correct "=Parameters!$B$50:$B$58".

Why is there this disconnect between oTempRange and Selection, when the selected cell is certainly within the oTempRange at the time?

Why is the oTempRange validation formula missing its sheet reference?

And most importantly, why is the validation not being set when I do this, and how do I get it to be set correctly?

Thank you for reading this, and please let me know if I left out any information you need to help me!
-Dan

[davesexcel]

Hi,
In most cases, using a range from another worksheet for data validation would require that range to be named, you would then use the named range as the list.

[daylward]

Hi,
In most cases, using a range from another worksheet for data validation would require that range to be named, you would then use the named range as the list.

Okay, spent a few hours creating a named range for the source list to try it that way.

The reason it took a few hours is because I got carried away and figured out how to make it a dynamic range, and in the end I came up with a named range "ProjectsValidation" using this range definition:
=OFFSET(Parameters!$B$50,0,0,MATCH(TRUE,INDEX(ISBLANK(Parameters!$B$51:$B$1000),0,0),0),1)
... which dynamically changes the range size to match the length of the list until the first blank cell, and I'm very pleased with how it works and proud of that so I had to post it here... bear with me. ;-)

Anyway, once I created the dynamic range and set the validation of the last cell in the invoice column to that range, i tried running my cleanup macro again. It still didn't work, with similar symptoms - no errors, but the validation did not get copied. Stepping through the code, though, to check the values, I noticed something. Outside the whole subroutine, I had this line:

Please Login or Register  to view this content.

I was doing this, and then reactivating the "Invoice" sheet when I was done, to make the screen redraw faster and smoother. While I was stepping through the code, I manually switched back to the "Invoice" sheet to check my cell validation values, and then it worked! When I commented out the line that activated the "Parameters" sheet, then ran it straight (without stepping through), it worked perfectly.

I believe this solution has to be a combination of using a named range and making sure the sheet containing the cells with validation is activated. It couldn't *only* be due to the sheet not being activated, since before, when I was using a specified (not named) range, I was manually activating the sheet while stepping through the code, and it still was not working.

I hope this experience helps someone avoid some hours of frustration, and thanks for the suggestion, davesexcel!

Dan