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.
_________________
Michael, you can change the code so it checks the target’s row and column:
Hi Debra, It works perfect! Thank you so much again for sharing your expertise.
weikee
How can I make this work for Excel 2007?
Hi, this is fantastic. I am very novice and have a small problem. I used adding to the same cell macro and changed column to column 6. I also want to run the same macro but from column 8 in the same spreadsheet, but I get an error message. i have tried simply calling it a different name but obviously i am doing something wrong. I would appreciate any help you can give me. Keep up the good work, this is fab. Thanks, Anna
boeh, the macro should work in Excel 2007. What problem are you having? Did you enable macros?
Tinks57, in the code, you can change the line that mentions the column, to include both columns. For example,
These tools are really great. I cna see many great uses for these. How would I modify the code to get a combination of samecell + line break. I’m looking to have multiple selections show up in the selection cell and seperated by a line break (not the cell to the right of the selection cell)
Thanks.