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.
_________________
George….your not alone.
I to am in the same boat. Debra – How about some detailed instructions for us non-programmer types. The funtions your codes performs seem to be great but it appears it is more complicated than doing a copy-paste with the code.
In my case I want to use the “samecell” code.
I have a column of cells that already have data validation that use a list (drop down list) for them. I copy/paste the “samecell” code to the sheet, changed the column number to match my column, closed and returned to Excel, made sure macro’s were enabled and got nothing! I even saved the file as a macro-enabled workbook (.xlsm) which did nothing. Closed and reopend and still nothing.
PLEASE……help us. 🙁
Debra,
I promise to buy your book (everyone probably will), can you helh the above me with the Multiselect question? And, are there these kind of examples in one your books? (I believe you have several).
Thanks,
Mark
Mark, a ListBox uses completely different code than what we’re doing here, with data validation.
For examples, see Dick Kusleika’s post on Multiselect Listboxes:
http://www.dailydoseofexcel.com/archives/2004/04/27/using-multiselect-listboxes/
Rick and George, I’ve recorded a video on setting up the data validation drop downs, and adding the worksheet event code.
It will be posted on the blog tomorrow, so I hope that will help you.
Many Thanks..very useful! 🙂
Help. I got the same cell (comma separated) to work. Closed my spreadsheet. I have reopened teh spreadsheet and its disappeared. I’ve replicated what you have said but can’t get it to work.