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.
_________________
I have used the code and it works awesome. But can someone plzz help me with a code for the same thing to work on “Open Office”. The same code does not work. I have used it but connot implement it ‘cauz many systems in my office has Open Office so the sheet is on hold.
Debra – You are my hero!!! Thank you so much for this code, tutorial, thread posts, etc! I’m a complete programming novice, and it works! Next comes List Boxes!
Thanks a million!
Hi Debra:
I looked at your videos and they are awesome! Thank you! I however have a small problem. I am trying to create a spreadsheet that allows me to choose from a list (say apple, orange, lemon, grapes etc). I would like the output to be more than one selection. In other words, I would like it to allow me to select apple and orange and display it as apple, orange. I downloaded your example and it did not allow me to do that. Do you have a location where there is an instruction for that?
Fantastic! The comma delimited Data Validation just saved me loads of time! Thank you.
I do have a question. Is there a why to conditionally remove the comma? For instance, my list is:
EXCEPTIONS
Auto
General
OTHER
Liquor
Service
I would like a Colon to come after the All Caps selections, rather than a comma in the list so as to read as follows:
EXCEPTIONS: Auto, General, OTHER: Liquor
Is this doable?
Big thanks!!!
LB in GA
I am actually trying to fill out an excel document that has a list box in it. I am prompted to select all answers that apply, however, I can not figure out how to select more than one choice. Have tried everything I can think of. Please advise!
This script dosn’t work for office 2003 ? Any suggestions ? Thanks.