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.
_________________
Cameron, you’re welcome! Glad you got it working.
I have downloaded the zipped file but when I go to try the different tabs they are not working. When I copied the SameCell coding to my spreadsheet it worked great but I wanted to try some of the other methods and now none of them work. Help!
John, make sure to enable the macros when you open the sample file, and then the different tabs should work.
Thanks for your response. I must have glitched something because after I closed all my open Excel files and re-opened Excel and everything worked fine.
I need your help on some of the programming. I want to limit the programming to a specific column because in other columns I want to use a regular pull down menu. This is the first time I have ever enter any type of programming in Excel! What additional programming language would I need to use to limit the CommaSeparated, LineBreak and SameCell programming to a specific column (Column 10 in my spreadsheet)?
Thanks!
John, most of the examples refer to a column number, e.g.
If Target.Column = 3 Then
Change that column number to 10, and it should work the way you want.
Hi Debra, Your script on this multiple selection on drop down list is very useful. For the “SameCell” sample, one issue I encountered is when you selected a wrong entry and tried to delete it, entry can not be deleted but instead the cell will contain “Old value” + “new value”.
Sample:
Old Value = “One, Three” {then I want to delete “Three” by selecting “Three” and press Delete , then press Enter}
New Value = One, Three, One (which is the Old + New)
Hope you can help me with this bug. Thank you.