Multi Select & Dependent Drop Downs

[Missile64]

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 

[#2]

@Missile64 

See the attached version. I implemented 2. and 3.

You can implement 1. by changing the ranges in the Worksheet_Change event procedure.

[#3]

@HansVogelaar 

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 

[#4]

@Missile64 

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.

[#5]

@HansVogelaar 

Thanks Hans, much appreciated.

[#6]

@Missile64 

What should be the source for Column6? Would it be the same as for Column2? Or do you want other options? If the latter, you should set up ranges with those options in columns C to G on sheet 2, for example starting in row 10.

By the way, why did you mark your own reply as the best answer?

[#7]

The same as column 2 would be good thanks.
Marking my reply was in error, wanted to mark your first response.

[#8 · Accepted solution]

@Missile64 

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)

[#9]

@HansVogelaar 

Works perfectly, thanks very much.

Pete

[#10]

Hans,
One last question. With regards to the Data Validation Drop downs in Columns B & F what is entered into the 'Source: field' when setting up the Data Validation List?
To test it I tried to also include Column G as a dependent drop down. I this part of the code from 'Call UpdateValidation(Target, 1, 5)' to Call UpdateValidation(Target, 1, 5, 6). But having issues with creating the Drop Down list in Column G.

Thanks
Pete

[#11]

@Missile64 

Specify List in the allow drop down, then enter a space in the Source box.

The VBA code will change the source when you select something in column A.

[#12]

Ah so simple, thanks very much.