You’ve probably used an Excel data validation drop down list, where you can click the arrow, and select an item to fill the cell. With a bit of programming, you can customize a drop down list, to select multiple items.
Single Selection Drop Down
By default, data validation drop downs only allow you to select one item.
In the worksheet shown below, you can select a month from the drop down, and it overwrites any previous selection in that cell.
Select Multiple Items From the Drop Down List
Instead of limiting users to a single selection, you can use a bit of programming, combined with the data validation list, and allow multiple selections.
You can display all the selected items across a row, or down a column, or keep them in the same cell.
Keep reading, to see some examples of this technique, and how it works.
There is also a video that shows the multiple selection drop down list. To try it for yourself, follow the download link f
Watch the Data Validation Video
To see these techniques, and a few other multiple selection examples, you can watch this short video.
_________________
Thank you so much for this code, Debra. While I’ve done a lot of coding for Microsoft Access in Visual Basic, I’m just getting started learning how to do so in Excel and your entry here was a real help.
I do have one question, though. Let’s say I have two spreadsheets:
Faculty Spreadsheet
Course Sections Spreadsheet
On the Faculty Spreadsheet, each faculty member has an ID number (see below)
Faculty Spreadsheet:
Faculty Name; Faculty ID
Mickey Mouse; 10111
Donald Duck; 10112
Uncle Scrooge; 10113
Launchpad McQuack; 10114
On the Course Sections Spreadsheet, I’ve set up column 2 so that it will pull the faculty names using your code. What I’d like it to do now is have column 3 pull in the Faculty IDs.
Below is an example of how I want the end result to look. Any ideas?
Course Sections Spreadsheet:
Section; Faculty; Faculty ID
ENG-101; Mickey Mouse; 10111
ENG-102; Donald Duck, Uncle Scrooge; 10112, 10113
ENG-103; Donald Duck, Launchpad McQuack, Mickey Mouse; 10112, 10114,
10111
Hi Debra.
Your code is awesome! I used and modified your csv separated example to my needs and it’s almost perfect now. Even the protection is working. Now, as my output cell is protected, the user cannot alter the content of this cell. In your sample “SameCellAddRemove” you can remove entries from the output cell by re-selecting the wrong one. How can I use this on the CSV seperated script?
Thanks so much!
AlexH
I really appreciate this VBA. I’m totally new to it, but just copying and pasting and a quick edit of column numbers and it worked!
Now, wondering if you can tell me how I can compare two cells that are populated by selecting items from the same list, albeit perhaps similar OR different items? May not be in same alphabetical order based on when selected. Would be much appreciated.
Hi Debra,
I used your code to enable selection of multiple values in the same cell, and it worked perfectly thanks..
But I’d like to be able to protect other cells in the worksheet, at the moment this stops the VB running so I can only select a single value again. Could you give me any tips on how to modify the code so that the VBA still runs when the sheet protection is turned on.
Thanks so much.
@MarkJ, at the start of the code, you can turn off the protection, then turn it on again, at the end of the code.
For example:
ActiveSheet.Unprotect Password:=”yourpwd”
‘code here
exitHandler:
ActiveSheet.Protect Password:=”yourpwd”
I’ve tried the code above for turning off the protection and turning back on again in Excel 2007, but it doesn’t work. I get the message “invalid outside procedure”. I’ve tried it exactly as above, with my password, with and without quotations. Any additional thoughts?
Your walkthrough and example worksheet are great!~ Very much appreciate all the effort you have put into helping everyone!~
I have tried the code change LB suggested above for having same cell line break.
I replaced:
Target.Value = oldVal _
& “, ” & newVal
With:
Target.Value = oldVal _
& vbLf & newVal <<<——
And it is not working the code turns red.
Any help is GREATLY appreciated!~
@Amber, your code looks fine, assuming you don’t really have the arrow at the end of the line. Do you have a space before the underscore character?
I am unable to edit the zipped zile to have the functions work in my spreadsheet. Even if i copy the code and delete those two lines to enable the whole document to select multiple items in a single cell (from the val. list), it fails to let me select more than one item? What is wrong? I am crunched for time and really need this function on a business trip and it will save me so much time.
thanks.