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.
_________________
@Stephen, there is another video that describes how to add the code to your workbook, and adjust it, if necessary:
http://blog.contextures.com/archives/2011/04/29/how-to-set-up-multiple-selection-excel-drop-down/
@Luis, you might need to change the code so it looks in all columns for the data validation drop down lists. This video shows how to modify the code:
http://blog.contextures.com/archives/2011/04/29/how-to-set-up-multiple-selection-excel-drop-down/
Debra
Thank you for this video. It works!
Follow-up question:
What if we wanted the same type of code listed within the same spreadsheet for column 3 and perhaps column 4 etc…
In other words, with your code, column two accepts multiple responses, can column three, four and five do the same within the same sheet?
@Stephen, yes, you can change the code to work in multiple columns. Here is the revised “If Intersect” section:
If Intersect(Target, rngDV) Is Nothing Then ‘do nothing Else Select Case Target.Column Case 3, 4, 5 Application.EnableEvents = False newVal = Target.Value Application.Undo oldVal = Target.Value Target.Value = newVal If oldVal = “” Then ‘do nothing Else If newVal = “” Then ‘do nothing Else Target.Value = oldVal _ & “, ” & newVal End If End If End Select End IfSo Awesome!! I figured it out, of course, with the help of copying and pasting. Now, I wanted it listed with line breaks instead of commas. I can’t find an answer 🙁
I’m having trouble applying this code. If the columns I want the code applied to are 8, 10, and 12, do I specify that in the line that curently reads “Case 3, 4, 5”? That’s what I have tried to do but Excel isn’t accepting the code like that.
When I try to manipulate the code for my project, I get an error message, followed by highlighting of the line “Private Sub Worksheet_Change(ByVal Target As Range)”. Any futher advice on making this code work for me?
Charles, do you have two Worksheet_Change procedures on the sheet? You can only have one with that name.
This code did not work. I copied directly in to the visual basic and nothing changed. Any thoughts?
Hi Debra, Brilliant Macros, very well explained and video. I am using the ‘SameCell’ Macro and have amended the columns to the ones that I need.
Just wondering if you have come up with a solution for the following, I know they have been listed & asked for before, but thought you might of solved the problems :
1. Selecting multiple items from a list without the drop down disappearing each time you pick a item from a list.
2. Ability to edit the cell so that if you want to delete or unselect a item from within the cell that may or may not be within the list it doesn’t add the new values and keep the old values as well.
3. Ability so that if you have already selected a item from within the list if you try & select it again it tells you or even better still any items that have already been selected are listed at the top of the list and are highlighted.
Thanks
Keep up the fantastic work
Just found another request, when you click on the drop down list is there a way that the whole list is shown and not just 8 items with a scroll down bar.
Thanks