[Latest update: July 27, 2016] With a bit of Excel VBA programming, you can change an Excel data validation drop down list, so it allows multiple selections. This post is a roundup of articles on how to set up multiple selection Excel drop down lists.
Multiple Selection Drop Downs
You can find a sample file and instructions, in a blog post that I wrote a couple of years ago – Select Multiple Items from Excel Data Validation List.
There is a video in that post, that shows how the multiple selection options work, and a peek at the code that makes the multiple selections possible.
Setting up the Workbook
Even though the post is a couple of years old, it still gets comments and questions. This week, Rick asked for details on setting up a new workbook, and copying the sample code into the new file.
To help, I’ve recorded another video, showing the setup steps.
More Articles on Select Multiple Items
[Update]: Since posting this article on selecting multiple items from an Excel drop down list, I’ve added more articles on the same topic. Please read these updates, for more details on working with the VBA code:
- edit multiple selection cells
- prevent duplicates in multiple selection column
Changing the Macro Code
If you need to change the column numbers in the code, there are instructions here.
To run the code on a protected worksheet, see the notes here.
Download the Sample Data Validation File
To experiment with this technique, you can download the zipped sample file from the Contextures website, on the Excel Data Validation – Select Multiple Items page.
_____________
I am getting the code to work when I type in the values in the Data Validation box, however, when I try to use values from a separate sheet within the same workbook it will not allow multiple values. Is there some code that I need to change to make this happen? Thank you in advance.
Thank you for this code. I am Adding Values to the Same Cell. It works with limitations. I am using a workbook with multiple spreadsheets. This workbbok will serve as a set of templates for a project which are distinguished by a tab within the worksheet. The sheets in the workbook are identical. One cell on each has my multiple-selection data validation criteria. When I make the selection on one sheet it is fine, but occasionally a bug arises when I try to clear the cells in a sheet to re-enter the data. Sometime the cells lock and other times the deleted data appears on other sheets in the workbook on the data validation field.
Thank you so much for sharing this tutorial and your code! It is extremely clear and easy to follow. You saved me hours of headache! Thanks!!!
Hi, This has been brilliant. I have the same question as others. Just wondering how i can apply this to muliple colulmns? If anyone can help out, that would be awesome. Thanks!
has anyone received info on the 2010 version question, and/or applying to multiple columns, and/or using a reference list (vs typing in a list)?
This code is great, but I can’t delete a row when use it in my workbook. When I autofilter and delete rows based on my criteria, the actually rows don’t delete, but the rows at the bottom of my table do. Please help!
This is a great code I need it to do this on the same worksheet for multiple columns, I use
IF Target.Column = ActiveCell.Column Then
This will also keep you from having to update the column when copying from page to page
Thanks again