Forum Discussion
Multi Select & Dependent Drop Downs
See the attached version.
UpdateValidation now lets you specify an arbitrary number of other columns to be affected, by specifying their offsets.
In the Worksheet_Change event procedure,when column A is modified, the line
Call UpdateValidation(Target, 1, 5)
updates the data validation source for the columns 1 and 5 to the right of column A, i.e. columns B and F.
If you wanted to add column H (7 to the right of column A), you'd use
Call UpdateValidation(Target, 1, 5, 7)
See the attached version. I implemented 2. and 3.
You can implement 1. by changing the ranges in the Worksheet_Change event procedure.
Thanks very much for your reply. Your changes are exactly what I am after.
I was able to include other multi select columns but what I don't understand are;
1. How the dependent drop down in Column 2 work and created.
2. In the attached example, how would I make Column 6 also Dependent on selections in Column 1 or any other multi select column?
Thanks again
Pete
It is not possible to use a range as source for the data validation, since that must be a single contiguous range. Since you allow multiple selections, the source would have to be the union of several ranges.
So the code in the worksheet module assembles the source in the form of a comma-delimited text string - see the UpdateValidation procedure.
If you want Column6 to be dependent on Column1, it becomes much more complicated - the current code relies on the dependent column being immediately to the right of the mult-select column it should depend on. So the code will have to be rewritten substantially.
Stay tuned.
Thanks Hans, much appreciated.
