I’ve done another update to the Data Validation Multiple Selection sample, thanks to a question from Pat, in a blog comment. He has 3 columns with different drop down lists, and wants to add new items to the applicable lists.
I can get 3 separate columns to each allow multiple selects from their own lists and to allow write in contents…But what I want …is to make the selected columns (not all) so that write in selections are written to the lists and the lists are sorted.
So, in the new example, on the SameCellAddSort worksheet in the sample workbook, that’s what happens. You can do a multiple select in columns C and D, but only a single selection in column B.
Add New Items
New items can be added to the Names and Numbers columns. If a new name is entered in column C, is will be added to the NameList range on the Lists sheet. Then, the list is sorted alphabetically.
Download the Sample File
To see the code, you can go to the Excel Data Validation – Select Multiple Items page on my Contextures site. The file contains macros, so enable them when opening the workbook.
Watch the Multiple Selection Add Sort Video
To see how the multiple selection add and sort technique works, and see an explanation of the code, you can watch this Excel video tutorial.
More Information on Data Validation Multiple Selection
Most of the examples in the DataValMultiSelect file have limits set in the code. For example, the code might check the column number, and only allow the multiple selections if the column number is 3.
If you remove those checks from the code, and just check for data validation in the selected cell, you could have several multi-selection lists on the same worksheet. There is an explanation on my Contextures website, for adjusting the code:
Here are a few more article on the data validation multiple selection technique:
- How to Set up Multiple Selection Excel Drop Down
- Edit Multiple Selections in Excel Drop Down Lists
- Select Multiple Items from Excel Data Validation List
_______________
I was pleased to be able to set up a multi-select drop-down for a single column on one Sheet based on your code. However, someone wants to have another column on the SAME sheet also be multi-selectable. I.E., in Column A, select on models of dream cars you’d like to have. Column B is select on the cars you can afford.
Adding the code in twice and changing the column number for the second instance didn’t work.
Is this possible? Or, do I have to create a On Event macro to take the person entering to a different sheet, then populate the original sheet with the multiple selections?
Thanks for any help you can give me.
Betsy
@Betsy, most of the examples in the DataValMultiSelect file have limits set in the code. For example, the code might check the column number, and only allow the multiple selections if the column number is 3.
If you remove those checks from the code, and just check for data validation in the selected cell, you could have several multi-selection lists on the same worksheet. There is an explanation on my Contextures website, for adjusting the code:
Excel Data Validation – Select Multiple Items
Deb – It worked right out of the gate. Thanks!!!
@Betsy Great! Love it when things work the first time.
Someone suggested one step further – use Conditional Formatting on the columns that should NOT be multi-selectioned since now all of them have the capability. That worked well, too.
Any cell with a comma (separating the selections) changes colors. (I just have to insure that none of the selections have commas in their name.)
Debra,
This is fantastic! Following on from Betsy I was wondering whether the code can be modified to limit multi-selection for specific columns? I have drop down lists in a number of columns on a work sheet but only need the multiselect in a few of them.
I had modified the code to
If Target.Column = 3 Or 5 or 7 Then
but this seemed to allow multi-selection in all columns instead of restricting it to the specific columns 3, 5 and 7. I’m guessing it’s not that simple…?
Apologies I’ve seen the solution on another of your blogs! Thank you though this is great work. 😀
I have the same query I want to add multiple drop down lists for different columns can someone help me with the coding .
Thanks
Hello! Pl refer your ‘SameCellAddRemove’ sheet in ‘DataValMultiSelect’ excel. This is amazing and fantastic excel. In that sheet I want that after selecting the data it should be sorted out within the cell. I mean if I am selecting One, Three & Four so it should come in its sequence only in any condition. If I am selecting ‘One’ then ‘Three’ & ‘Four’ so it takes it correct – One, Three, Four but when i deselect One and again select it then it comes like this – Three, Four, One. So I want that it should be sorted out. Hope you understood my query. Thanks in Advance.
I want to have a validation list based on values from another sheet, but those values are not in one sequence i-e one value is in A2 another value is in A12 and third is in A15 how will i make it
Please if anyone knows it let me know urgently
Hello,
First of all thanks for the sample.
Code doesen’t work if i want to protect sheets.
What shoud i do?
@Michael, I’ve added a new code sample that will work on protected sheets
http://www.contextures.com/excel-data-validation-multiple.html#protect