One of my colleagues asked me if it is possible to make multiple selections in a drop-down list in Excel.
When you create a drop-down list, you can only make one selection. If you select another item, the first one is replaced with the new selection.
He wanted to make multiple selections from the same drop down in such a way that the selections get added to the already present value in the cell.
Something as shown below in the pic:
There is no way you can do this with Excel in-built features.
The only way is to use a VBA code, which runs whenever you make a selection and adds the selected value to the existing value.
Watch Video – How to Select Multiple Items from an Excel Drop Down List
How to make Multiple Selections in a Drop Down List
In this tutorial, I will show you how to make multiple selections in an Excel drop-down list (with repetition and without repetition).
This has been one of the most popular Excel tutorials on this site. Since I get a lot of similar questions, I have decided to create an FAQ section at the end of this tutorial. So if you have any questions after reading this, please check out the FAQ section first.
There are two parts to creating a drop-down list that allows multiple selections:
- Creating the drop-down list.
- Adding the VBA code to the back-end.
Creating the Drop Down List in Excel
Here are the steps to create a drop-down list in Excel:
- Select the cell or range of cells where you want the drop-down list to appear (C2 in this example).
- Go to Data –> Data Tools –> Data Validation.
- In the Data Validation dialogue box, within the settings tab, select ‘List’ as Validation Criteria.
- In Source field, select the cells which have the items that you want in the drop down.
- Click OK.
Now, cell C2 has a drop-down list which shows the items names in A2:A6.
As of now, we have a drop-down list where you can select one item at a time (as shown below).
To enable this drop-down to allow us to make multiple selections, we need to add the VBA code in the back end.
The next two sections of this tutorial will give you the VBA code to allow multiple selections in the drop-down list (with and without repetition).
VBA Code to allow Multiple Selections in a Drop-down List (with repetition)
Below is the Excel VBA code that will enable us to select more than one item from the drop-down list (allowing repetitions in selection):
Private Sub Worksheet_Change(ByVal Target As Range)
'Code by Sumit Bansal from https://trumpexcel.com
' To make mutliple selections in a Drop Down List in Excel
Dim Oldvalue As String
Dim Newvalue As String
On Error GoTo Exitsub
If Target.Address = "$C$2" Then
   If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
   GoTo Exitsub
   Else: If Target.Value = "" Then GoTo Exitsub Else
       Application.EnableEvents = False
       Newvalue = Target.Value
       Application.Undo
       Oldvalue = Target.Value
       If Oldvalue = "" Then
           Target.Value = Newvalue
       Else
           Target.Value = Oldvalue & ", " & Newvalue
       End If
   End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
Now you need to place this code in a module in VB Editor (as shown below in the ‘Where to put the VBA code’ section’).
When you have placed this code in the backend (covered later in this tutorial), it will allow you make multiple selections in the drop down (as shown below).
Note that if you select an item more than once, it will be entered again (repetition is allowed).
Try it Yourself.. Download the Example File
VBA Code to allow Multiple Selections in a Drop-down List (without repetition)
A lot of people have been asking about the code to select multiple items from a drop-down list without repetition.
Here is the code that will make sure an item can only be selected once so that there are no repetitions:
Private Sub Worksheet_Change(ByVal Target As Range)
'Code by Sumit Bansal from https://trumpexcel.com
' To allow multiple selections in a Drop Down List in Excel (without repetition)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Address = "$C$2" Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = "" Then
Target.Value = Newvalue
Else
If InStr(1, Oldvalue, Newvalue) = 0 Then
Target.Value = Oldvalue & ", " & Newvalue
Else:
Target.Value = Oldvalue
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
Now you need to place this code in a module in VB Editor (as shown in the next section of this tutorial).
This code will allow you to select multiple items from the drop-down list. However, you will only be able to select an item only once. If you try and select it again, nothing would happen (as shown below).
Try it Yourself.. Download the Example File
Where to Put the VBA Code
Before you start using this code in excel, you need to put it in the back-end, such that it gets fired whenever there is any change in the drop-down selection.
Follow the below steps to put the VBA code in the backend of Excel:
- Go to the Developer Tab and click on Visual Basic (you can also use the keyboard shortcut – Alt + F11). This will open the Visual Basic Editor.
- There should be a Project Explorer pane at the left (if it is not there, use Control + R to make it visible).
- Double click on Worksheet Name (in the left pane) where the drop-down list resides. This opens the code window for that worksheet.
- In the code window, copy and paste the above code.
- Close the VB Editor.
Now when you go back to the drop-down and make selections, it will allow you to make multiple selections (as shown below):
Try it Yourself.. Download the Example File
Note: Since we are using a VBA code to get this done, you need to save the workbook with a .xls or .xlsm extension.
Frequently Asked Questions (FAQs)
I have created this section to answer some of the most asked questions about this tutorial and the VBA code. If you have any questions, I request you to go through this list of queries first.
Q: In the VBA code, the functionality is for cell C2 only. How do I get it for other cells? Ans: To get this multiple selection drop-down in other cells, you need to modify the VBA code in the backend. Suppose you want to get this for C2, C3, and C4, you need to replace the following line in the code: If Target.Address = "$C$2" Then with this line: If Target.Address = "$C$2" Or Target.Address = "$C$3" Or Target.Address = "$C$4" Then
Q: I need to create multiple drop-downs in entire column 'C'. How do I get this for all the cells in the columns with multi-select functionality? Ans: To enable multiple selections in drop-downs in an entire column, replace the following line in the code: If Target.Address = "$C$2" Then with this line: If Target.Column = 3 Then On similar lines, if you want this functionality in column C and D, use the below line: If Target.Column = 3 or Target.Column = 4 Then
Q: I need to create multiple drop-downs in a row. How can I do this? Ans: If you need to create drop-down lists with multiple selections in a row (let's say the second row), you need to replace the below line of code: If Target.Address = "$C$2" Then with this line: If Target.Row = 2Â Then Similarly, if you want this to work for multiple rows (let's say second and third row), use the below line of code instead: If Target.Row = 2Â or Target.Row = 3 Then
Q: As of now, the multiple selections are separated by a comma. How can I change this to separate these with space (or any other separator). Ans: To separate these with a separator other than a comma, you need to replace the following line of VBA code: Target.Value = Oldvalue & ", " & Newvalue with this line of VBA code: Target.Value = Oldvalue & " " & Newvalue Similarly, if you want to change comma with other character, such as |, you can use the following line of code: Target.Value = Oldvalue & "| " & Newvalue
Q: Can I get each selection in a separate line in the same cell? Ans: Yes you can. To get this, you need to replace the below line of VBA code: Target.Value = Oldvalue & ", " & Newvalue with this line of code: Target.Value = Oldvalue & vbNewLine & Newvalue vbNewLine inserts a new line in the same cell. So whenever you make a selection from the drop-down, it will be inserted in a new line.
Q: Can I make the multiple selection functionality work in a protected sheet? Ans: Yes you can. To get this done, you need to do two things: Add the following line in the code (right after the DIM statement): Me.Protect UserInterfaceOnly:=True Second, you need to make sure the cells - that have the drop-down with multiple selection functionality - are not locked when you protect the entire sheet. Here is a tutorial on how to do this: Lock Cells in Excel
You May Also Like the Following Excel Tutorials:
Hi,
i have read your drop-down multiple selection post (without repetitions). it worked perfectly fine,but whenever i am closing worksheet all data validation get deleted how to solve this problem.
thank you in advance.
Hello,
When making multiple selections, how do you then delete a selection you may have accidentally clicked?
that is also what I was going to question. Only deleting entire value works currently.
Hi,
If the sheet is protected, multiple drop down list is not working ? any thoughts please
edited code so that the list removes a duplicate element if that happens: https://pastebin.com/N9RGFkBZ
AWESOME! This deletes the unwanted list item via “reselecting it” in the dropdown. Thank you!
This worked great for me
I plug in the VBA code and changed it to If Target.Column = H, but it does not work. I still cannot select multiple answers.
Hi – you need to replace H with the number of the column which in this case is 8
Hi, I recently changed the Columns numbers in the VBA code because the applicable Columns changed location and now I’m getting compile errors. However, it worked just fine before I changed the column numbers. After the error it highlights the “.Column” portion of the first “If Target.Column” instance. Not sure why I’m getting this error. I had changed the VBA column numbers several times before this while creating the Workbook and it worked fine, but something is messed up now. Please help.
This code worked for a moment yesterday, then my file crashed and the code has not worked since… Incredibly frustrating…
Is there a way to count the items later on if they are listed in a multiple list?
If you know please share
=IF(C3=””,””,(LEN(TRIM(C3))-LEN(SUBSTITUTE(TRIM(C3),”,”,””))+1))
(You put that formula in a separate cell)
Hi Sumit
I have two queries.
1. I tried your solution and put the list in the same sheet (A3-A5) as the cell (E3) where I wanted the multiple selection, but its not working for some reason. It is just picking 1 value. The sheet is IssueLog.
2. I have the Standard list in one Worksheet ‘ReferenceInfo’ and I want to implement the multiple selection in another worksheet ‘IssueLog’ in column E (E3 to E300). Can it be done?
I downloaded the sample to select multiple items. When selecting items, only one displays. What needs to be done to activate the display of multiple items?
I have to add multiple questions some that requires only one answer and other multiple. How can you create list without all of them being multiple selection.
You will have to change the code so that only the cells where you want multiple selections are included in the code
I have just made my excel worksheet Marco enabled and added the code to the code window saved and closed. I went to select multiple choices from the list and still can only select one
Private Sub Worksheet_Change(ByVal Target As Range)
‘Code by Sumit Bansal from https://trumpexcel.com
‘ To allow multiple selections in a Drop Down List in Excel (without repetition)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Address = “$C$2” Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = “” Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = “” Then
Target.Value = Newvalue
Else
If InStr(1, Oldvalue, Newvalue) = 0 Then
Target.Value = Oldvalue & “, ” & Newvalue
Else:
Target.Value = Oldvalue
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
hi Sumit,
can you clarify the following. I have my spreadsheet with multiple columns on sheet 1 and the dropdown list choices which is to be used for column F (on sheet 1) is listed on Sheet 2. Would I paste in the code above on the VBA Editor sheet 1 or 2?
Hey Smruti, you need to paste the code for the sheet that has the drop-down list. So in your case, it would be sheet 1
thank you, it worked!
Hello, sorry if this question has been asked before. How about selecting multiple entries in the dropdown listbox ?
Something like ctl + click or so to select multiple entries at once.
Tia.
/Dirk
I’m really thankful to you as this code too useful for me, but i have a one query when my sheet is protect then this code isn’t work.
Please help me out i just want to use this code if my sheet is protect with password.
Hi Sumit, @sumitbansal23
Its very useful tutorials from your website.
I have used one of your https://trumpexcel.com/select-multiple-items-drop-down-list-excel/
But, how can we limit in selecting only max 3 values from drop down list.
Please help.
HI, Happy 2018!! I downloaded the example file but it is not working. Any security settings in Excel that I must change? Thank you.
If I have to use the code for two columns specifically, say column D and
column I which have different sets of values in the respective drop down lists, how will the code change? This is the code from your website I am using now:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
‘Code by Sumit Bansal from https://trumpexcel.com
‘ To Select Multiple Items from a Drop Down List in Excel
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
‘If Target.Address = “$F” Then
If Not Intersect(Target, Range(“I:I”)) Is Nothing Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = “” Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = “” Then
Target.Value = Newvalue
Else
If InStr(1, Oldvalue, Newvalue) = 0 Then
Target.Value = Oldvalue & “, ” & Newvalue
Else:
Target.Value = Oldvalue
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
I saw code to get this to work while the sheet is protected, and that worked great. However, when I shared the workbook it stopped working and kept giving debug pop-ups. Is there a way to use this function while sharing a protected workbook?
I have indirect lookups off the back of a drop down and if i choose multiple options in the first cell then my lookup no longer works, is there a solution to this?
Hi Sumit
How do I protect the code form getting change by other user
This is a little bit late, but I just came across this and have a couple questions. Is there a way to limit the amount of selections one can make in a drop down list to say, 5? Currently there’s roughly ~200 items in the list and I only want them to be able to select a maximum of 5 of those at a time. Also, say I have the same list in all of column 7 and 8, would I be able to have a command that tells the user that once something is selected in column 7 they can no longer select it in column 8 or vice versa?
Thanks!
Hi Sumit – Thank you for this great information! I have an additional question… We have a worksheet with many picklists, and I am using this code for all multi-select picklists. We do allow the user to enter a new value if needed. Then we use “circle invalid data” to find the new values that have been added. However, when using “circle invalid data” on the multi-select picklists, it ALWAYS circles the cell if there is more than one value entered, even if they are valid choices. Is there a way to get “circle invalid data” to work properly with the multi-select columns? If not, is there something we could add to the code, to “highlight” those values that the user added (that are not valid)?
Thanks so much for your help!
I’m curious on how one can create dynamic multiple items list. For example… let us say we have the following:
Column 1 Drop Down
Colors: Blue, Yellow
Column 2 Drop Down
Blue: Light Blue, Medium Blue, Dark Blue
Yellow: Light Yellow, Medium Yellow, Dark Yellow
Column 2 is dependent to Column 1.
Thus, if I pick “Blue” in column 1 then in column 2 I have the choice to pick Light Blue and/or Medium Blue and/or Dark Blue
If I picked “Blue” and “Yellow” in column 1 then in column 2 my options to pick are:
Light Blue and/or Medium Blue and/or Dark Blue and/or Light Yellow and/or Medium Yellow and/or Dark Yellow
Thank you.
Thanks so much! Really useful, and just saved me hours of brain hurt!
Hi there!
First off, many thanks for the code, it made my research that much easier! I was just wondering whether this will be compatible when computing statistical analysis in Studio R; can I conduct tests on excel cells with multiple items?
Thanks in advance,
Mana
Hello,
Thanks you for this code, it works fine !
I used it on an entire column, and I want to filter by choice (say if i choose “one, two, three”, I want to that cell to come up if I filter for “two”). Is that possible ?
Every time I save the workbook, my coding disappears when I reopen the document. Can someone please help???
Thanks for the solution..its works fine…however it is being applied to all the cells in the sheet and I am not able to edit the cells even where there is no drop down menu to choose from. Can i choose the columns to which this code should be applicable?
This solution is OK, but what if you want to delete values? So https://uploads.disquscdn.com/images/f49a89e68134ae908e184e590a5b4e052ec9548648b51162600b703cceefebff.jpg I have produced an advanced solution.
YOu can dowload the code for free. It uses check-boxes and is far more useful.
http://www.vlsiip.com/exceltips.html
I have now developed the whole VBA solution using Listboxes, uploaded it on my web site:
http://www.vlsiip.com/exceltips.html
https://uploads.disquscdn.com/images/f49a89e68134ae908e184e590a5b4e052ec9548648b51162600b703cceefebff.jpg
I cannot get this to work, even with the file I downloaded. I enabled the macros and still did not work. I am using Excel 2016. Could this be the issue? Do you have a solution?
Thank you
http://www.vlsiip.com/exceltips.html
Download it from here, it is free easy to understand. It uses Listboxes and checkboxes. Will enable you to select multiple values from this drop down list menu.
Hi,
Im using the multiple selection dropdown list. I don’t know how to describe but hopefully with my example below will make you to understand :-
In my dropdown list have a several option where each option have their own values.
grape – 4
apple – 3
banana – 2
orange – 1
when i choose in the dropdown for Grape,Banana,Apple the excel will look for the lowest value among the option i had choose. In this case, the excel will find that Banana have the lowest value among the option i had choose hence the value showed up is 2.
How can do that?
Hi, is there any way to add scrolling feature to the drop down?
https://uploads.disquscdn.com/images/3d702f5a3a9b4018d71bd888549aae80b2837a66664a4dab41f92a0b2b29f6d8.png
Hi, I’ve used the code successfully but now it does not work after an update of Excel version 15.39 (171010). It can only contain one value in the field. Should I change something in the code to make it work again?
Anyway to limit the number of entries? I’d like to only be able to select a maximum of 5 entries. I have a formula in F5 on the sheet that counts the number of separators (I used “;” instead of “, “) that adds 1 since for 5 entries there would only be four semi colons. I want a message box to appear when F5 has a value of 5 and then exit the sub.
Hi I have tried this code and still cannot select more than one drop down from my list. are you able to help.
Thanks so much very easy to follow and worked the first time.
Well done!
Is there a way to do this using a key combination instead of a comma to delimit the selections?
Dear sir,
Can you help me up how to loop range till 2 to 5000 for below code
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
If Target.Address = “$C$2” Or Target.Address = “$D$2” Then
If Target.Address = “$C$2” Then
Range(“E2”) = Range(“E2”) + Target
ElseIf Target.Address = “$D$2” Then
Range(“E2”) = Range(“E2”) – Target
End If
Target = “”
End If
Application.EnableEvents = True
End Sub
Sub Evenement()
Application.EnableEvents = True
End Sub
Hi. This has been extremely helpful, but I have my drop down list on one sheet and the cell on another. How do I code for that? I am also trying to use a date picker so that when my teachers click on a cell, they can have a calendar pop up to click on. Can you address that issue or tell me where I can find the answer? Thank you.
If I change the address to
If Target.Column = 1 Then
The function does not work. what might I be doing wrong?
Hi, I was able to get the code to work, but when an email marco was attached to the spreadsheet it quit working. What have I done wrong?
How can I count the number of each selection? I tried countif but it does not seem to be working.
I’m having a difficult time implementing this solution for my particular use case. In my spreadsheet, I am applying data validation on the fly first — in other words, every time I click on a cell in a given range on my sheet “User Lists” it checks the header of that column, looks for that value in the header row on “User Picklists” and then if it finds it it uses the list from that page as the list for data validation on User Lists. Some of the columns need to be Multi-Select though, so once that code block runs, I have used yours immediately below it.
However, it’s not working the way I expect it to even though I left the code almost identical to how you are using it above. The difference is in your sheet, the code fires when I select a value from the list. In my sheet, it fires as soon as I click the cell and doesn’t re-fire when I select the value. I believe this has to do with the other code block above it, but I’m not sure how to make your block re-fire when I select the value. Do you have any tips? See full code below:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
Dim ws As Worksheet
Dim RefRng As Range, RngFind As Range, NewRng As Range, hdr
Dim RefList As Range, c As Range, rngHeaders As Range, Msg
On Error GoTo ErrHandling
Set ws = ThisWorkbook.Worksheets(“User Picklist”)
‘only deal with the selected cell(s)
Set NewRng = Application.Intersect(Me.Range(“A12:T101”), Target)
If Not NewRng Is Nothing Then
Set rngHeaders = ws.Range(“A11:ZZ11″)
For Each c In NewRng
c.Validation.Delete ‘delete previous validation
hdr = Me.Cells(11, c.Column).Value
If Len(hdr) > 0 Then
Set RngFind = rngHeaders.Find(hdr, , xlValues, xlWhole)
‘matched header?
If Not RngFind Is Nothing Then
Set RefList = ws.Range(RngFind.Offset(1, 0), _
RngFind.Offset(1, 0).End(xlDown))
c.Validation.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Formula1:=”='” & ws.Name & “‘!” & RefList.Address
End If ‘matched header
End If ‘has header
Next c
End If ‘in required range
‘Multi Select
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Not NewRng Is Nothing Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = “” Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = “” Then
Target.Value = Newvalue
Else
If InStr(1, Oldvalue, Newvalue) = 0 Then
Target.Value = Oldvalue & “, ” & Newvalue
Else:
Target.Value = Oldvalue
End If
End If
End If
End If
Here:
Application.ScreenUpdating = True
Exit Sub
ErrHandling:
If Err.Number 0 Then
Msg = “Error # ” & Str(Err.Number) & ” was generated by ” & _
Err.Source & Chr(13) & “Error Line: ” & Erl & Chr(13) & Err.Description
Debug.Print Msg, , “Error”, Err.HelpFile, Err.HelpContext
End If
Resume Here
End Sub
Hi. Nice post. I am looking for a dropdown list where I can (really) multi-select. e.g. holding the Ctrl or Shift key. Here with your solution I have to select each one-by-one. Ctrl-A to select all would be also nice.
I have a workbook that needs different dropdowns in all columns and down 10-20 rows. Underlying data is on a separate sheet. Which sheet should the code be posted and how do I change the code to accomodate 20 different drop down lists
Hi VBA code is not working in my excel sheet
Private Sub Worksheet_Change(ByVal Target As Range)
‘Code by Sumit Bansal from https://trumpexcel.com
‘ To Select Multiple Items from a Drop Down List in Excel
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Address = “$C$2” Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = “” Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = “” Then
Target.Value = Newvalue
Else
If InStr(1, Oldvalue, Newvalue) = 0 Then
Target.Value = Oldvalue & “, ” & Newvalue
Else:
Target.Value = Oldvalue
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
what wrong with my excel
Hi Sumit,
This is a great code and for the most part it’s worked well for me. I’m trying to apply this to a range of cells from B3:H1012. Can you please advise what part of the code needs to be changed? I would also appreciate if you can let me know the code it needs to be changed with. Thanks a lot in advance!!!
Hello, this code works great. Thank you for sharing this great work. Unfortunately, I’ve come across an issue not yet addressed here. I need to protect the worksheet, but once I do that the code no longer works. Is there a solution for this? Thank you!