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.
_________________
Excellent. This is just what I needed. I actually extended this a bit so that the default entry on the list always says “pick from list”.
Next thing to add would be a feature that goes through the already created list of values from the drop-down list and exclude those values on the drop-down that have already been added.
Hello ingeno79, i have been trying to add a default entry. Would you mind sharing that code with me?
Thanks ingeno79, adding a default entry is a good idea.
To exclude the items that are already added, you could combine this with the technique in this tutorial:
Hide Previously Used Items in Dropdown
How do you select multiple items to more than just one column?
@Hadji, you can change the code to include multiple columns with Select Case.
There is sample code in my comment here:
http://blog.contextures.com/archives/2009/09/18/select-multiple-items-from-excel-data-validation-list/#comment-13540
How do I filter the column by individual results?
For instance if column A has the following cells:
A1: header
A2: apple, orange, grape
A3: orange, apple
A4: grape
I want to use the auto-filter option so that if I filter to show only “orange”, I will get A2 and A3. If I filter to show only “grape”, I will get A2 and A4.
This works great, but I have moved the script into a new workbook and each time I open this new workbook and try to make a change in the dropdown list a warning comes up to say that the cell or chart is protected. I can unprotect the sheet by going to tools and protection, but each time I open it i get the same error message. I need to remove this automatic protection which seems to come into effect on open so that I can have other users use my sheet without getting warnings.
Your help would be much appreciated.
SP, there’s code in the ThisWorkbook module, that runs when the workbook opens. Perhaps you copied that to your new workbook too. If so, just delete that, and the SameCell sheet won’t be protected automatically.
Thanks Debra – works great now.
Debra,
Great work on the multiple item validation. I was wondering if there was anyway that I could change the macro for “LineBreak” such that I could limit it to a single or multiple columns but not the entire spreadsheet.
Thanks,
Steve