Forum Discussion
Multi Select & Dependent Drop Downs
I have been trying to create a number of multi select drop downs and a dependent drop down (which is sourced from one of the multi select drop downs). The attached example I have Column 1 & 2 as multi select and column 2 is also a dependent drop down.
I found some coding online that does 75% of what I would like it to do.
The following is what else I would like the code to do if possible;
1. Ability to Target Columns as per this code (which I cannot get to work with the code in the attachment) "If Destination.Column <> 4 And Destination.Column <> 6 Then GoTo exitError"
2. Ability to remove selections (if a mistake is made it can be removed without having to delete all of the selections in the cell and starting again)
3. The Dependent drop down (column B) only shows the options of the last cell in Column A updated, eg If I populate cells A2 then A3 and then i goto B2 to make selection, the options I have are dependent of the selection made for cell A3 not its corresponding cell A2. Can the code be modified so that when a cell is selected in column B it checks the corresponding cell in column A and display the correct options for selection?
4. Sort each cell alphabetically (not important if not possible).
Thanks in Advance
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.
