by The article shows how to create an Excel drop down that allows users to select multiple items with or without duplicates.
Excel has come a long way since its inception and introduces more and more useful features with each new release. In Excel 365, they've added the ability to search within data validation lists, which is a huge time-saver when working with large sets of data. However, even with this new option, out-of-the-box Excel still only allows selecting one item from a predefined list of options. But fear not, as there is a solution. By using VBA, you can create drop-down lists with multiple selections. With the ability to prevent duplicates and remove incorrect items, this feature can streamline data input and improve accuracy in your Excel spreadsheets.
How to make Excel drop down with multiple selections
Creating a multi-select drop down list in Excel is a two-part process:
- First, you make a regular data validation list in one or more cells.
- And then, insert the VBA code at the back end of the target worksheet.
It also works in the reverse order :)
Create a normal drop-down list
To insert a drop down list in Excel, you use the Data Validation feature. The steps slightly vary depending on whether the source items are in a regular range, named range, or an Excel table.
From my experience, the best option is to create a data validation list from a table. As Excel tables are dynamic by nature, a related dropdown will expand or contract automatically as you add or remove items to/from the table.
For this example, we are going to use the table with the plain name Table1, which resides in A2:A25 in the screenshot below. To make a picklist from this table, the steps are:
- Select one or more cells for your dropdown (D3:D7 in our case).
- On the Data tab, in the Data Tools group, click Data Validation.
- In the Allow drop-down box, select List.
- In the Source box, enter the formula that indirectly refers to Table1's column named Items.
=INDIRECT("Table1[Items]")
- When done, click OK.
The result will be an expandable and automatically updatable drop-down list that only allows selecting one item.
Tip. If the method described above is not suitable for you for some reason, you can create a dropdown from a regular range or named range. The detailed instructions are here: How to create Excel data validation list.
Insert VBA code to allow multiple selections
This is the core part of the process that does the magic. To turn a regular single-selection picklist into a multi-select dropdown, you need to insert one of these codes in the back end of your target worksheet:
- VBA code for multi-select drop down with duplicates
- VBA code for multi-select drop down without duplicates
- VBA code for multi-selection dropdown with item removal
To add VBA code to your worksheet, follow these steps:
- Open the Visual Basic Editor by pressing Alt + F11 or clicking the Developer tab > Visual Basic. If you don't have this tab on your Excel ribbon, see how to add Developer tab.
- In the Project Explorer pane at the left, double-click on the name of the worksheet that contains your drop-down list. This will open the Code window for that sheet.
Or you can right-click the sheet's tab and choose View Code from the context menu. This will open the Code window for a given sheet straight away.
- In the Code window, paste the VBA code.
- Close the VB Editor and save your file as a Macro-Enabled Workbook (.xlsm).
That's it! When you go back to the worksheet, your drop-down list will allow you to select multiple items:
VBA code to select multiple items in dropdown list
Below is the code to make a data validation list that allows selecting multiple items, including repeated selections:
How this code works:
- The code enables multiple selections in all drop down lists on a particular sheet. You do not need to specify the target cell or range reference in the code.
- The code is worksheet specific, so be sure to add it to each sheet where you want to allow multiple selections in drop down lists.
- This code allows repetition, i.e. selecting the same item several times.
- The selected items are separated with a comma and a space. To change the delimiter, replace ", " with the character you want in DelimiterType = ", " (line 7 in the code above).
Note. The same character cannot be used as both the delimiter and part of the dropdown items. In our code, the default delimiter is a comma followed by a space (", "), so this combination of characters should not appear anywhere within the dropdown items themselves to avoid conflicts. If you change the delimiter to a single space (" "), bear in mind that the code will only function correctly for single-word items, failing to handle multi-word items containing spaces.
Excel multi-select dropdown without duplicates
When selecting from a large list, users can sometimes pick the same item more than once without noticing. The code below solves the problem of duplicates in a multi-selection data validation drop down list. It lets users pick a particular item only once. If you try to select the same item again, nothing will happen. Pretty cool, right?
Multi-selection dropdown with item removal
When users need to select multiple options but can make mistakes or change their minds during the selection process, a multi selection dropdown that allows for the removal of incorrect items can be a lifesaver.
Consider a scenario where you need to assign multiple tasks to team members using a drop-down list. With Excel's default functionality, the only way to remove an incorrectly assigned task is by clearing the entire contents of the cell and starting over. With the ability to remove individual items from the selection, the team can effortlessly modify task assignments without confusion or errors.
The code below implements the item removal functionality in a simple and intuitive way: the first click on an item adds it to the selection, and a second click on the same item removes it from the selection.
The below demo highlights how the multi selection dropdown with removal functionality works in Excel. The users can select multiple options from the data validation list and make adjustments on the fly. A streamlined and effective approach to managing selections!
How to make a multiple selection dropdown with custom delimiter
The character that separates items in the selection is set in the DelimiterType parameter. In all the codes, the default value of this parameter is ", " (a comma and a space) and it is located in line 7. To use a different separator, you can replace ", " with the desired character. For instance:
- To separate the selected items with a space, use DelimiterType = " ".
- To separate with a semicolon, use DelimiterType = "; " or DelimiterType = ";" (with or without a space, respectively).
- To separate with a vertical bar, use DelimiterType = " | ".
For example, if you change the delimiter to a vertical slash, your multi-select picklist will look as follows:
How to create dropdown with multiple selections in separate lines
To get each selection in a separate line in the same cell, set DelimiterType to Vbcrlf. In VBA, it is a constant for the carriage return and line feed characters.
More precisely, you change this code line:
DelimiterType = ","
to this one:
DelimiterType = vbCrLf
As a result, each item that you select from the dropdown list will appear in a new line:
How to create multi-select dropdown for specific columns, rows, cells and ranges
All the codes described in this tutorial work across an entire sheet. However, you can easily modify any of the codes, so it only applies to specific cells, rows, or columns as needed. For this, find this line of code:
If rngDropdown Is Nothing Then GoTo exitError
Add immediately after it, add a new line specifying where to allow multiple selections, as explained in the below examples.
Multi-select drop-down for specific columns
To allow selecting multiple items in a certain column, add this code:
If Not Destination.Column = 4 Then GoTo exitError
Where "4" is the number of the target column. In this case, the multi-select dropdown will be only enabled in column D. In all other columns, dropdown lists will be limited to a single selection.
To target several columns, specify their numbers using this code:
If Destination.Column <> 4 And Destination.Column <> 6 Then GoTo exitError
In this case, the multi-select dropdown will be available in columns D (4) and F (6).
Multi-selection dropdown for certain rows
To insert multiple drop-downs in a specific row, use this code:
If Not Destination.Row = 3 Then GoTo exitError
In this example, replace "3" with the row number where you want to enable multi-select dropdowns.
To target multiple rows, the code is as follows:
If Destination.Row <> 3 And Destination.Row <> 5 Then GoTo exitError
Where "3" and "5" are the rows where selecting multiple items is allowed.
Multiple selections in specific cells
To enable multiple selections in particular cells, add one of the below code lines.
For a single cell:
If Not Destination.Address = "$D$3" Then GoTo exitError
For multiple cells:
If Destination.Address <> "$D$3" And Destination.Address <> "$F$6" Then GoTo exitError
Just remember to replace "$D$3" and "$F$6" with the addresses of your target cells.
Multi-select drop-down for specific range
To limit your multi-select dropdown to a particular range, replace this line of code:
If Intersect(Destination, rngDropdown) Is Nothing Then
with this one:
If Intersect(ActiveCell, Range("C3:D10")) Is Nothing Or Intersect(Destination, rngDropdown) Is Nothing Then
The range of interest is specified directly in the code (C3:D10 in the above example). This modification offers a more efficient approach to handing ranges - instead of individually listing 16 cells, you use a single range reference.
How to enable multi-selection functionality in protected sheet
To enable a multi-select dropdown functionality in a protected worksheet, simply insert the following code into the sheet where you've added the primary code.
Before adding this code to your worksheet, remember to replace "password" with the actual password you used to protect the sheet. And this is the only change that needs to be made. The code will automatically detect the presence of a dropdown list in a given cell and allow editing of that cell. In all other cells, editing will remain restricted.
Note. Please be aware that including your real password in the code could lead to a security risk. To ensure the safety of your workbook, store it in a secure location that is protected against unauthorized access or use.
So, there you have it - an Excel dropdown list with multiple selections. Adding this awesome feature to your spreadsheets will increase the accuracy of your data input and help you get your work done faster!
Practice workbook for download
Multi-selection dropdown - examples (.xlsm file)
465 comments
How can I make a dropdown of a specific cell range associated with a cell containing a specific text where the text can vary based on a dropdown? For example, there is a data sheet that contains a column titled Product that has 3 products; another column titled Module that has 19 options each of which is associated with a specific product; a third column with 85 Agents who in turn are associated with specific modules. I'm trying to make a dropdown that if I select "x" module, it will only list the agents associated with that module. By the same token, a dropdown that would only list the modules associated with a specific product.
Hi! We have a special tutorial that can help to solve your problem: How to make a dependent (cascading) drop-down list in Excel.
Alex, this is amazing! Thank you! Quick question: is there a way to make a multi-selection available to only one column in a worksheet and have other data validation be single selection? Thanks.
Hi! Pay attention to the following paragraph of the article above: Multi-select dropdown for specific columns, rows, cells
How can I adapt the 'Multi-selection dropdown with item removal' code you provided to make it work for 2 columns in the worksheet (column 11 & column 12) ONLY. They both have separate drop-down lists, I want people to be able to multi-select more than 1 thing from the drop down list but give them chance to remove things selected by mistake without having to delete and start again. When I added If Destination.Column 4 And Destination.Column 6 Then GoTo exitError after section specified it didn't work and errored at bit of code with LEFT and RIGHT in ikt
Hi! If you need the code to work in columns 11 and 12, specify those column numbers in the code, not 4 and 6. All the necessary information is in the article above.
Really useful information here. I would like to know if there is a way to put this code in an addin and enable all workbooks to have access to the code instead of copying the code to the worksheet. I know the code is workbook worksheet specific but how can you remove this limitation?
Hi! Unfortunately, Worksheet_Change only works on a worksheet.
This code works great, thank you for the time spent creating this! My one question, i used the Multi-Select version of your code and its tailored to one column, column 3 but i would like to sort the results alphabetically in each cell that has multiple items selected, Im guessing i need a sort command between the last two "End If" is this correct? Can you provide an example?
Thanks again for the well crafted code.
Hi! A drop-down list creates a text string in a cell. To sort individual words in the text, you must write special VBA code. Or you can split the text into cells, then sort and merge the text strings again.
Or you can use the TEXTSPLIT function to split the text, and then combine the text strings using the TEXTJOIN function. For example:
=TEXTJOIN(", ",TRUE,SORT(TEXTSPLIT(A1,,", ")))
I am enabling this function for 2 cells in a protected worksheet. However, this function for multiple selection with removal is working only when it's unprotected. My code is pasted below, thank you so much!
'https://www.ablebits.com/office-addins-blog/create-multi-select-dropdown-excel/
Option Explicit
'Multi-selection dropdown with item removal
'https://www.ablebits.com/office-addins-blog/create-multi-select-dropdown-excel/
Private Sub Worksheet_Change(ByVal Destination As Range)
Dim rngDropdown As Range
Dim oldValue As String
Dim newValue As String
Dim DelimiterType As String
DelimiterType = vbCrLf
Dim DelimiterCount As Integer
Dim TargetType As Integer
Dim i As Integer
Dim arr() As String
If Destination.Count > 1 Then Exit Sub
On Error Resume Next
Set rngDropdown = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitError
If rngDropdown Is Nothing Then GoTo exitError
'define only the cells need to enable multiple selection
If Destination.Address Range("Destination_Expiring_Coverage").Address And Destination.Address Range("Destination_Renewing_Coverage").Address Then GoTo exitError
TargetType = 0
TargetType = Destination.Validation.Type
If TargetType = 3 Then ' is validation type is "list"
Application.ScreenUpdating = False
Application.EnableEvents = False
newValue = Destination.Value
Application.Undo
oldValue = Destination.Value
Destination.Value = newValue
If oldValue "" Then
If newValue "" Then
If oldValue = newValue Or oldValue = newValue & Replace(DelimiterType, " ", "") Or oldValue = newValue & DelimiterType Then ' leave the value if there is only one in the list
oldValue = Replace(oldValue, DelimiterType, "")
oldValue = Replace(oldValue, Replace(DelimiterType, " ", ""), "")
Destination.Value = oldValue
ElseIf InStr(1, oldValue, DelimiterType & newValue) Or InStr(1, oldValue, " " & newValue & DelimiterType) Then
arr = Split(oldValue, DelimiterType)
If Not IsError(Application.Match(newValue, arr, 0)) = 0 Then
Destination.Value = oldValue & DelimiterType & newValue
Else:
Destination.Value = ""
For i = 0 To UBound(arr)
If arr(i) newValue Then
Destination.Value = Destination.Value & arr(i) & DelimiterType
End If
Next i
Destination.Value = Left(Destination.Value, Len(Destination.Value) - Len(DelimiterType))
End If
ElseIf InStr(1, oldValue, newValue & Replace(DelimiterType, " ", "")) Then
oldValue = Replace(oldValue, newValue, "")
Destination.Value = oldValue
Else
Destination.Value = oldValue & DelimiterType & newValue
End If
Destination.Value = Replace(Destination.Value, Replace(DelimiterType, " ", "") & Replace(DelimiterType, " ", ""), Replace(DelimiterType, " ", "")) ' remove extra commas and spaces
Destination.Value = Replace(Destination.Value, DelimiterType & Replace(DelimiterType, " ", ""), Replace(DelimiterType, " ", ""))
If Destination.Value "" Then
If Right(Destination.Value, 2) = DelimiterType Then ' remove delimiter at the end
Destination.Value = Left(Destination.Value, Len(Destination.Value) - 2)
End If
End If
If InStr(1, Destination.Value, DelimiterType) = 1 Then ' remove delimiter as first characters
Destination.Value = Replace(Destination.Value, DelimiterType, "", 1, 1)
End If
If InStr(1, Destination.Value, Replace(DelimiterType, " ", "")) = 1 Then
Destination.Value = Replace(Destination.Value, Replace(DelimiterType, " ", ""), "", 1, 1)
End If
DelimiterCount = 0
For i = 1 To Len(Destination.Value)
If InStr(i, Destination.Value, Replace(DelimiterType, " ", "")) Then
DelimiterCount = DelimiterCount + 1
End If
Next i
If DelimiterCount = 1 Then ' remove delimiter if last character
Destination.Value = Replace(Destination.Value, DelimiterType, "")
Destination.Value = Replace(Destination.Value, Replace(DelimiterType, " ", ""), "")
End If
End If
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
exitError:
Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.UnProtect Password:=strPwd
On Error GoTo exitError2
If Target.Validation.Type = 3 Then
Else
ActiveSheet.Protect Password:=strPwd
End If
Done:
Exit Sub
exitError2:
ActiveSheet.Protect Password:=strPwd
End Sub
If you don't have enough knowledge in VBA, just follow the instructions in the article above. There is no value assigned to the strPwd variable.
I have two sheets in my workbook. One is for tracking inventory and the other contains all the various select lists for different columns of data in the inventory sheet. For example, categories, subjects, status, etc. I can match-up the single select columns and lists across the two sheets but, I'd like the "subject" column to allow multiple entries as explained here. How to I tell it which sheet & column in the List sheet contains the data I want to use in a specific column in the Inventory sheet?
Clarification, How do I tell it which column in the List sheet contains the data I want to select from and add to a specific column in the Inventory sheet.
Hi! As described in the article above, you can specify a sheet, column, or individual cells in which the drop-down list can make multiple selections.
How do I combine the code for "VBA code to select multiple items in dropdown list" and "How to enable multi-selection functionality in protected sheet"
Option Explicit
Private Sub Worksheet_Change(ByVal Destination As Range)
Dim DelimiterType As String
Dim rngDropdown As Range
Dim oldValue As String
Dim newValue As String
DelimiterType = ", "
If Destination.Count > 1 Then Exit Sub
On Error Resume Next
Set rngDropdown = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitError
If rngDropdown Is Nothing Then GoTo exitError
If Intersect(Destination, rngDropdown) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newValue = Destination.Value
Application.Undo
oldValue = Destination.Value
Destination.Value = newValue
If oldValue = "" Then
'do nothing
Else
If newValue = "" Then
'do nothing
Else
Destination.Value = oldValue & DelimiterType & newValue
' add new value with delimiter
End If
End If
End If
exitError:
Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Unprotect password:="password"
On Error GoTo exitError2
If Target.Validation.Type = 3 Then
Else
ActiveSheet.Protect password:="password"
End If
Done:
Exit Sub
exitError2:
ActiveSheet.Protect password:="password"
End Sub
Hi! You have declared Worksheet_SelectionChange procedure twice. This is incorrect in VBA. Delete the code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub
I used the code below after your suggestion to remove the piece you mentioned. It's still not working, it won't let me select the multiple values - I see it protecting the sheet after entering the code, but won't allow for multiple value selection. I'm wondering if my add in of the protection code is in the wrong place? would you be able to combine the 2 codes into one as it's supposed to run and I can copy and paste that into excel? I would be so greatful for the assistance.
Option Explicit
Private Sub Worksheet_Change(ByVal Destination As Range)
Dim DelimiterType As String
Dim rngDropdown As Range
Dim oldValue As String
Dim newValue As String
DelimiterType = ", "
If Destination.Count > 1 Then Exit Sub
On Error Resume Next
Set rngDropdown = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitError
If rngDropdown Is Nothing Then GoTo exitError
If Intersect(Destination, rngDropdown) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newValue = Destination.Value
Application.Undo
oldValue = Destination.Value
Destination.Value = newValue
If oldValue = "" Then
'do nothing
Else
If newValue = "" Then
'do nothing
Else
Destination.Value = oldValue & DelimiterType & newValue
' add new value with delimiter
End If
End If
End If
exitError:
Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Unprotect password:="password"
On Error GoTo exitError2
If Target.Validation.Type = 3 Then
Else
ActiveSheet.Protect password:="password"
End If
Done:
Exit Sub
exitError2:
ActiveSheet.Protect password:="password"
End Sub
Hi! I have your code working without problems on a protected worksheet. Check on which worksheet this code is installed. Also try the recommendations on how to enable macros from the previous comment.
Thank you for the tutorial. I have copied the code exactly, and the first time I used it it worked Perfectly, However, after saving it as a Macro enable excel sheet (as instructed) it no longer allows the multiple selection. I re-copied the code and it is still not working. Do you have any suggestions on how to correct this?
Hi! I am unable to verify your file. However, I hope this information will help to solve the problem: How to enable and disable macros in Excel.
I tried the code, but it does not work with me. I then downloaded your file, in fact multi selection does not work in your file as well. There is no error displayed, but it keeps showing one selection only. Do I have any issue on excel setting or else?
Can anyone help?
Hi! Your Excel has macros disabled. Here is the article that may be helpful to you: How to enable and disable macros in Excel.
Hi Alexander,
Wow, this is spectacular well written information. Thank you very much. I have a question about Multiple selections in specific cells. In you example you list just two cells, however what would be the process for several (5) cells.
Hi! Add as many And Destination.Address <> "[address]" conditions to the code as necessary.
I have another question, I have a drop-down menu In which I have 3 variables like resistanxe, Voltage, current, in second column B1
If one of the variables is selected I would like to have two variables pop upoutside this list under the $B$1 in $B$2, $B$3,
Those $B$2, $B$3have their own variables like for $B$2 coffin Manson, Arhenius, but they should disappear if $B$1 ( current is selected). Or a other variable in $B$1. So if I have $B$1 on resistance= I will get two strings variables in $B$2, $B$3, if values are written in $C$2; $C$3; they should write it to the second sheet for a calculation. And stay there. They will be permanently saved somehow. I didn’t find a good solution for this. Please help or refer to a good explanation
Hi! Your explanation is not quite clear. However, you can get the values in B2 and B3 depending on the value in B1 either by using the IF formula or by using VBA. These pieces of info are not enough to give you a formula.
This is incredibly helpful! Wow thank you for sharing.
I am trying to reference the various values selected in the drop down. Before, when it was just one value it was fairly straightforward. Now with multiple values I'm having trouble splitting them apart to be referenced. Any tips?
Hi! The macro creates a text string of multiple values in a cell. You cannot reference part of the text string, only the entire cell.
This tutorial was immensely helpful however, I am having an issue with 365 with the protected sheet code. Prior to protecting the sheet, the VBA code allows multi select in column 16 & 23 (without the additional protect code). Once the sheet is protected with these columns unlocked, I can only single select. I unprotected the sheet, added the additional VBA for protecting the sheet (edited the password), protected the sheet again, and have the same result- the sheet no longer allows multiselect once the protection is initiated.
I read a previous message from a user who had the same issue but did not see a resolution. This is the code in my sheet- is there something I should be adding or an additional step I am missing? Thank you.
Option Explicit
Private Sub Worksheet_Change(ByVal Destination As Range)
Dim DelimiterType As String
Dim rngDropdown As Range
Dim oldValue As String
Dim newValue As String
DelimiterType = ", "
If Destination.Count > 1 Then Exit Sub
On Error Resume Next
Set rngDropdown = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitError
If rngDropdown Is Nothing Then GoTo exitError
If Destination.Column 16 And Destination.Column 23 Then GoTo exitError
If Intersect(Destination, rngDropdown) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newValue = Destination.Value
Application.Undo
oldValue = Destination.Value
Destination.Value = newValue
If oldValue = "" Then
'do nothing
Else
If newValue = "" Then
'do nothing
Else
Destination.Value = oldValue & DelimiterType & newValue
' add new value with delimiter
End If
End If
End If
exitError:
Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Unprotect password:="password"
On Error GoTo exitError2
If Target.Validation.Type = 3 Then
Else
ActiveSheet.Protect password:="password"
End If
Done:
Exit Sub
exitError2:
ActiveSheet.Protect password:="password"
End Sub
I see the sheet unprotecting and protecting itself any time I use the dropdown, but multiselect is no longer possible.
Hi! In your code, instead of
If Destination.Column 16 And Destination.Column 23 Then GoTo exitError
must be written down
If Destination.Column <>16 And Destination.Column <>23 Then GoTo exitError
In this case, everything works for me.
Before installing the code, you can try to remove the protection from the worksheet. It will be automatically enabled with this code.
Hello, everything works great for me until I attempt to add the code for a protected sheet. I am inputting my password, but I am getting an error for an ambiguous name. I believe it is related to how the code is formatted. Any help would be appreciated!
To expand, when I remove the previous instance of the "worksheet_Selectionchange" I no longer get the error, but I cannot make multiple selections within my worksheet.
Hi! Your description of the problem is not very clear. I can assume that you are adding two Worksheet_SelectionChange codes to the sheet. There should be only one code with this name. To work on a protected sheet, make changes in the code as described in the article above.
Once my protected sheet code is added below the original code. I do not get any errors on a protected sheet, but the sheet works as if there has been no VBA code at all. In other words, when I attempt to add multiple selections, it only displays the most recent selection that has been chosen. I hope this helps to clear up the previous comment.
I am currently having the same issue. Once the sheet is protected, the multiselect is not available.
Hi! Unfortunately, I can't see your workbook. Worksheet_Change and Worksheet_SelectionChange code must be added to the protected worksheet. All this is described in detail in the article above.
Attached below is how I have the code structured. I hope this helps! I appreciate the quick responses!
Option Explicit
Private Sub Worksheet_Change(ByVal Destination As Range)
Dim rngDropdown As Range
Dim oldValue As String
Dim newValue As String
Dim DelimiterType As String
DelimiterType = ", "
If Destination.Count > 1 Then Exit Sub
On Error Resume Next
Set rngDropdown = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitError
If rngDropdown Is Nothing Then GoTo exitError
If Intersect(Destination, rngDropdown) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newValue = Destination.Value
Application.Undo
oldValue = Destination.Value
Destination.Value = newValue
If oldValue "" Then
If newValue "" Then
If oldValue = newValue Or _
InStr(1, oldValue, DelimiterType & newValue) Or _
InStr(1, oldValue, newValue & Replace(DelimiterType, " ", "")) Then
Destination.Value = oldValue
Else
Destination.Value = oldValue & DelimiterType & newValue
End If
End If
End If
End If
exitError:
Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Unprotect Password:="password"
On Error GoTo exitError2
If Target.Validation.Type = 3 Then
Else
ActiveSheet.Protect Password:="password"
End If
Done:
Exit Sub
exitError2:
ActiveSheet.Protect Password:="password"
End Sub
This code does not match the code suggested in the article and in the sample file linked at the end of the article.
I do not believe that the sample file includes the code for protected sheets. When adding the protected sheet code, I must delete the other mention of "worksheet_selectionchange" or I receive an ambiguous name error. The code above contains both the original code and protected sheet code, but it is obvious that it is inserted incorrectly as you have mentioned in the above comment. How should the protected sheet code be added to the original code?
You have correctly written how the code should be inserted. But you changed the macro code. Pay attention to
Destination.Value = newValue
If oldValue "" Then
If newValue "" Then
If oldValue = newValue Or _
Copy the code carefully.
I believe that error in the code is from copying and pasting the VBA code to this comment section. I confirmed that my code in excel is identical to the code in the above post. When the sheet is unprotected, the code works perfectly, but when the sheet is protected it only allows one item from the drop down to be added to each cell.
This tutorial was immensely helpful; I created a drop-box list and am able to select multiple choices. When I copied the Excel Worksheet to the next Sheet, this function doesn't copy over, even when I attempt to highlight the cells and reinsert the code. Is there an effective way to copy this functionality?
Hi! As noted in the article above, the code is specific to the worksheet, therefore be sure to add it to each worksheet where you want to allow multiple selections in the drop-down lists. Also, a worksheet can only have one VBA code from the three offered to you.
Yes, I saw that information and I have ensured to use only one type of VBA code, and confirmed that it is listed under each sheet tab in the Excel Worksheet, and it still only works for the first entry. Any type of troubleshooting may I perform to confirm if there is an error in the process of what I did?
Hi! If you copy entire worksheet, the macro code will be copied along with the data and will work. If you copy the data to a new blank worksheet, you need to set the code on that new worksheet as described in the article above.
Hi there -- I'm so grateful for this code and your straightforward instructions! I'm having a small issue. The code works perfectly on my first worksheet but not on my second worksheet. I've copied and pasted the same code from my first to my second but I don't get the multi-selection functionality. Is there something in the code that needs to change from one worksheet to the next? These are 2 sheets in the same Excel file (workbook). Much appreciated!
Hi! When inserting the code into the second sheet, you do not need to change anything in the code. If multiple selection does not work, then the code is inserted incorrectly.
Hi, for some reason I cannot get the multiple-selection options to work (even when I download the example docs that you have linked). Is there some setting in Excel that would prevent this option from working? Or is there a specific key that needs to also be pressed for multiple selections when you are in the dropdown (I tried doing control, that did not work).
Hi! You may have macros in Excel disabled. Maybe this article will be helpful: How to enable and disable macros in Excel.
Is there a way to share the file with others that has multiple selection so that they can edit the sheet?
Hi! There are two ways to share a macro in Excel - you can either send the workbook with macros enabled or the file containing the macro code. The recipient can then open the workbook or import the code file to utilize the macro. However, it is important to note that this approach may not be successful if the recipient has varying security settings or Excel versions.
Hello! Thank you for this info. I have a formula that attributes a monetary value to each selection of my drop down menu. Is there a way I can keep these value associations even with multiple selections? For example, If you choose "Party package A" from the drop down menu in Column F, then it will add $200 in column G. However if a person chose "Party Package A" along with "Balloon Garland", I'd like column G to total both the values of "Party package A" and "Balloon Garland"
Hi! This depends on the formula you use in column G. The multiple selection macro creates a text string in column F of all the values you selected in the drop-down list.
Now I got the problem that my other Formulars not work. How can I fix that I need them.
The multiple selections macro only works in cells that have a drop-down list. It cannot affect your formulas.
Only wanted to say thank you Alex for sharing with the rest of the world. This tutorial was extensive and included all possible variations for the same problem.
Hi Alexander
Thanks for your comprehensive useful post. Is there any way to allow multi-select from a dropdown list on excel online? For example, if I wanted to select more than one employee for a project.
Thanks in advance!
Hi! Excel Online does not support VBA. Therefore, no macros or custom functions work there. We have already written about it in the comments above.
Thanks so much for this - I used this to put together a spreadsheet for my boss. I noticed one issue - if I try to delete one item from my cells that have the multiple selection options, it will just copy all the options again. I have to delete all the contents of the cell if I make one mistake. I wonder if there is a way around that.
Hi! Your explanation of the problem is not quite clear. Multi-selection dropdown with item removal removes only one item from a cell.
I have copied the code exactly as posted with no success.
I then downloaded and opened the example document and enabled macros, even that file doesnt do it. Is there something I am missing or maybe a further excel setting that is disabling macros?
Hi! The following tutorial should help: How to enable and disable macros in Excel.
This was super helpful and instructed me how to do exactly what I wanted. However, I am now running into a new issue. I want to count the number of unique occurrences for all selections made from the multi-selection list. Whenever I use a count function, it does not count any occurrences in cells that have multiple selections. Is there a way that I can make it count all occurrences, whether it be a singular selection or part of a multi-selection cell?
Hi! A multiple choice drop-down list creates a text string in the cell that consists of all the values that have been selected. To determine if text strings partially match, use these instructions: How to find substring in Excel. To count all occurrences, try a formula:
=SUMPRODUCT(--ISNUMBER(SEARCH("zzzz",A1:A100)))
Hi. I have successfully applied your methods in a worksheet, it is really clear and helpful, thank you very much.
I noticed when I copied the answer to this question, Excel initially gave an error message, which I could resolve after changing the "," to an ";" (as in
=SUMPRODUCT(--ISNUMBER(SEARCH("zzzz";A1:A100))).
thank you for this wonderful lessons. however, i was able to create a dropdown list but could not enable multiple selections. i copied and paste the VBA code but i keep getting error . saying compile error invalid outside procedure.
i dont know what to do.
Hi! You have inserted the code incorrectly. You can use the sample file linked at the end of this article.
Hi Alex,
When I input the code from the "How to enable multi-selection functionality in protected sheet" section, I get the error message: Ambiguous name detected: Worksheet_SelectionChange." Any ideas on how to fix this?
Hi! Look carefully at your code. The Worksheet_SelectionChange procedure is written twice.
Love this and your thorough steps! Can this be used on a call that uses "offset" data validation? I've copied and pasted the code and it doesn't seem to still work. Wondering if that could be the problem?
Hi! I don't really understand what you want to do. This code works with any Excel dropdown list. This dropdown list can be created in different ways. Read more: How to create drop down list in Excel: static, dynamic, editable, searchable
I have a drop down list created that offers up options based on how some previous cells are filled out. I input the VBA code in the worksheet where the drop down list is but it doesn't work. So I was wondering if it was because that cell and the drop down list has an offset formula in that cell.
Hi! The code works with any dropdown list that can be created in the ways described in this article: How to create drop down list in Excel. Check to make sure you have installed the code correctly.
Nice in theory; however, I feel like there must be some details missing in your explanation, as I can't even get past the first step. I even tried replicating your exact example on a new spreadsheet (created shorter Table/list) and typing literally what you had down to brackets/no spaces/double quotes/square brackets/etc. (=INDIRECT("Table1[Items]") And I got an error message "The source evaluates to an error, do you want to continue"? Generally, I am highlighting/selecting a column or series of rows for the 'Source' field. Were the "Table1[Items]" just a placeholder for highlighting/selecting that actual options? I tried that too but always get the same error message.
Hi! Here is the article that may be helpful to you: Create drop down list in Excel: static, dynamic, editable, searchable.
Hi Alexander,
This page is amazing! Thank you so much!
I'm trying to make it so that in certain columns, the options selected are separated by a comma, while in certain columns the options are separated by line breaks. However, when I try to run it, I receive an error message saying: Ambiguous name detected.
Hi! You can only use one code and one type of delimiter on a worksheet.
I figured out if you do an "If Destination.Column = 4 Then / DelimiterType = "/" ...lines copied from section of website you want... End If" You can define a new delimiter for each column. You have to do it individually though. I couldn't group multiple columns into one if loop.
Hi! You can set a different type of separator for a column. Here is an example of how you can set different separators for columns D and F.
If Destination.Column = 4 Then
DelimiterType = "; "
End If
If Destination.Column = 6 Then
DelimiterType = " - "
End If
Great article. I only have one comment for possible refinement of instructions.
Your example has the look up and the data on the same sheet. I often put look ups on a separate sheet.
You instructions on where to put the VS code should say "Select the sheet where you'll be selecting from your look up". As it's worded, it's initially unclear whether to paste the code into the sheet with the look up or the data entry.
Hi! The article notes that the code is worksheet specific, so be sure to add it to every worksheet where you want to allow multiple selections in dropdowns.
Can the VBA scripts somehow we specified in module within a workbook rather than within specific worksheets of the workbook?
Hi! Worksheet_SelectionChange to control the change in cell value, available only on the worksheet.
Alexandr, you are the best! Spasibo :)
Hi, Alexander. Your material is super! Just one question, a bit more complex :)
I've created an Excel table with multiselection dropdown in a specific column. Based on this table, I've created a pivot table with several slicers, one of these slicers refers to the column D with multiselection dropdown.
When in a certain cell in the column D several options are selected, but in another cell only one of these options is selected, in my slicer I see different options for filtering. For example: "X", "Y", but also "X,Y,Z". Is it possible to create a slicer that shows unique options only but allow filter them in all cells in column D regardless if in the same cell other options are selected together with the one I want to filter?
How this could be done?
Thanks in advance, Natalia
Hi! "X,Y,Z" is a text string. You cannot search for unique elements within a text string. To do this, you must split the elements of the text string into separate cells. I hope I answered your question. If you have any other questions, please don’t hesitate to ask.
This was super useful! One thing i noticed about using the VBA for multiple selections is that is removes the ability to start typing the item name when searching for a dropdown item. I am working with a very long dropdown list, so this functionality really comes in handy. Is there any way to have the ability added back into the sheet?
Hi! To create searchable drop-down lists in Excel, use data validation and named ranges. The steps are described below:
1. Enter data into Excel columns. For example, if you want to create a searchable drop-down list of products, enter the product name in one column and the corresponding code in another column.
2. Highlight the column with the data and click the “Formulas” button on the ribbon at the top of the screen. There, click “Define Names” button and name the named range. For more details on how to create a named range, read in this article: Excel names and named ranges: how to define and use in formulas.
3. Highlight the cells for which you want the drop-down list to appear and click the “Data” button on the ribbon at the top of the screen. From there, select the “Data Validation” option.
4. In the Data Validation dialog box, select “List” as the validation condition. In the Source field, enter the named range you created in step 2.
5. Select the “Dropdown in cells” and “Ignore whitespace” check boxes.
6. Click “OK” to close the dialog box.
Now, when you click on a cell with a dropdown, a list of all the elements in the named range will be displayed. To search for specific items, you can type multiple letters and Excel will filter the list accordingly. Select an item and the corresponding value will appear in the cell.
We hope this is helpful to you!
Is there something that can be added to the code to also allow there to be 0 selections with item removal?
Hi! To have no options to select from the drop-down list, simply remove drop-down list.
Should the ability to select multiple items from the drop list remain if the cell with the drop list is unlocked but the sheet itself is protected? (It doesn't in my case. The celll reverts to only the last item selected.)
My apology!! You have instructions for this--and they work like a charm!!!
Hi! Pay attention to the following paragraph of the article above: How to enable multi-selection functionality in protected sheet.
It covers your case completely.
Unbelievable! This works like a charm, and I know nothing about VBA. Thank you sooooo much!
Can you tell me how to retain formatting of dates when multiple items are selected? The options are, for example, Aug-23, Sep-23, etc., but when multiple options are selected, the format changes to 8/1/2023 and 9/1/2023. I've tried date formatting and custom formatting to no avail.
Hi! When you select a date from the drop-down list, Excel adds it in the Short Date Format that is set in your computer's Local Settings. Try using dates written as text in the drop-down list.
Oh my word. You're a genius. Thank you for your quick response!
Multiple selection worked in Excel 2016, but next to each row with multiple selection it throws an yellow error icon to its left saying:
"The value in this cell is invalid or missing. ".
It wants the value to match one of the listed items (eg. not multiple selection)
The delimiter is comma+space. Nothing protected or locked.
Source: Jhon, Dave, Jim (any of these is fine but adding more than one, Excel complains with the yellow error symbol. The selection functionality works though. Tried all 3 vba options - same complaint
Hi! The macro works without errors in any version of Excel. Check how you followed the instructions in the article above.
Hey Alex, this is amazing, thank you!!
I am a teacher, I have given my students 4 stations to attend each week - they get to choose where they go - but once they have chosen that specific station, I don't want them to be able to see that choice again - does this make sense? and if it does, how do I do that? I have all the kids names on the left, then the choices in 4 columns across the top.
example:
Tuesday Wednesday Thursday Friday
THIS IS NOW Drop down choice (4 of them) Drop down choice (4 of them) Drop down choice (4 of them) Drop down choice (4 of them)
THIS IS WANT Chooses 1 Only sees 3 remaining choices Only sees 2 remaining choices Only sees last choice available
and then I want to be able to clear their choices and start fresh the following week.
I hope this makes sense and I am not even sure if what I am asking for makes any sense, but I have it does.
If I am really pushing my luck, I would LOVE for the students to access this by scanning a QR code so I am the only one that sees what they picked for that day so their "friends" can't see what they picked, and in a perfect world, I would love to only allow a certain number to choose that station each day - but I think that is asking for something that doesn't exist, hahaha.
Anyway, I appreciate in advance, any help you can give me!!!
Thanks,
Tracy
Thanks Alexander.
Could you please suggest the code changes for using the multi-select drop-down to a specific Excel table Column? That is instead of identifying and using the column number of the Excel table column in the code, can the code be modified to use Table Name and the specific Column Name (Header).
Appreciate your support.
Thank you so much for this. I do not know about VBA but the instructions are really clear and they work as needed.
Works just as I needed it to. Thank you so much for this and for including the extra versions too. At first I thought I wouldn't need them, then thought ok, I don't want duplicates, and then right, what I actually want is no duplicates with the easy ability to modify the list item by item. So cool to see these all being separately supported in an easy to use way. Thanks again!
Hello! Your coding has been super helpful at multiple points for a project I've been slowly improving upon for work on and off for a few years now, so, thank you so much for that!
On that note, is there a way the display of the dropdown selections can be forced to arrange in a specific way regardless of the order in which they were selected? This would make filtering much simpler for my project. For example: can they be ordered in alphabetical order or by the order in which they are displayed on the dropdown list?
Thanks and have a great day!
Nicole
Dear Alexander,
I am so impressed with this page, it's easy to understand, and mostly pre-empt the task and provide the solution. I was looking for this option for quite sometime, however mostly I got though google search, was partially solving my issues. However your solution over delivered. I wish you all the best .
Thanks,
Sukanta
Many thanks this helped me a lot
Hi again Alex, I am so dopey, I re-read your comments
"To get each selection in a separate line in the same cell, set DelimiterType to Vbcrlf. In VBA, it is a constant for the carriage return and line feed characters.
More precisely, you change this code line:
DelimiterType = ","
to this one:
DelimiterType = vbCrLf"
and deleted the " " and it fixed it.
Hi Alex, I have booked marked your page and find your knowledge on excel the most useful and insighting. I have nominate 3 columns and am changing the DelimiterType form"', " to = "vbCrLf" however when i save it, close it then reopen it shows this in the column; Abusive Behaviour Towards StaffvbCrLfAlcohol on Premises.
Please advise what i am doing wrong.
Hi! See the corresponding paragraph in the article above. You can also see the sample code in the example file linked at the end of the article.
Hi Alexander,
This page is incredible! Thank you so much!
I'm having some trouble though isolating the multiple dropdown selection to one column. In your instructions, you said about adding the code "If Not Destination.Column = 4 Then GoTo exitError". The only thing I changed was 4 to 5 as that's the column I need the dropdowns in.
However, it is still affecting my whole worksheet, so if I go to a dropdown in column 6 (F) where I only want a single selection, it allows me to put in multiple selections.
Please can you advise where I've gone wrong and offer a solution?
Sorry, I should have looked at the previous comments! For those that are having similar problems, I resolved the issue by inserting the following below the "If Destination.Count > 1 Then Exit Sub" line:
If Not Destination.Column = 5 Then Exit Sub
The worksheet now allows me to make my multiple selections in column 5 (E), and single selections in column 6 (F).
Hi! Pay attention to which sheet code you added this string of code to. The code works only on the sheet in which it is inserted.
Hi Alexander
thanks for your comprehensive useful post. I have a problem here, I did everything same as you've mentioned here, and it works as multiple selection with removal enabled, so I save my worksheet as macro enabled, but after I close and reopen the file, it won't work as multiple selection but only single selection
Hi! Your file has macros disabled. Save the file as xlsm. Try using these recommendations: How to enable and disable macros in Excel.
Hi Alexander, Thanks for the post very useful. Can we restrict selection of entries to 3 max based on comma's. May be stop at selected 3 entries and error when adding the 4th one. Appreciate if you could help with the code to use in the above example.
Hi! As much as I'd like to help, I'm overloaded with some current projects at the moment and won't be able to take time for your task. Custom code changes are quite time-consuming.
no problem. Thank you!