How to make Excel drop down list with multiple selections

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.

Excel drop down list with multiple selection

How to make Excel drop down with multiple selections

Creating a multi-select drop down list in Excel is a two-part process:

  1. First, you make a regular data validation list in one or more cells.
  2. 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:

  1. Select one or more cells for your dropdown (D3:D7 in our case).
  2. On the Data tab, in the Data Tools group, click Data Validation.
  3. In the Allow drop-down box, select List.
  4. In the Source box, enter the formula that indirectly refers to Table1's column named Items.

    =INDIRECT("Table1[Items]")

  5. When done, click OK.
Create a data validation list from a table.

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:

To add VBA code to your worksheet, follow these steps:

  1. 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.
  2. 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.

  3. In the Code window, paste the VBA code.
  4. Close the VB Editor and save your file as a Macro-Enabled Workbook (.xlsm).
Insert VBA code to select multiple items in dropdown list.

That's it! When you go back to the worksheet, your drop-down list will allow you to select multiple items: Excel drop-down list 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:

VBA code to select multiple items in Excel dropdown
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

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?

VBA code to create multi-select drop down with no repeats
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)   End Sub

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.

VBA code to create multi-select drop down that allows removing selected items
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 = ", " 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   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) Or InStr(1, oldValue, DelimiterType & 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)   End Sub

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!
Select and remove multiple items in Excel dropdown.

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: A multi-selection dropdown with a custom delimiter

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: A dropdown list with multiple selections in separate lines

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.

VBA code to allow multiple selections in protected sheet
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

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)

Table of contents

472 comments

  1. Thanks, sir. It's my first time trying macro. It is very useful for my project. All Thanks to your clear tutorial and code.

  2. Hey, is it possible to select a few items at one go from the dropdown list? For example, instead of selecting "Apples" , "Oranges", separately, is there a way to select multiple items at one go? So that Apples and Oranges will be displayed together at the same time without an extra step of selecting each item one by one. Perhaps like a checkbox before each item in the dropdown list.

  3. Hello. I used the VBA code in an Excel spreadsheet to allow multiple drop-down box selections in separate lines. Instead of DelimiterType = “,” I changed it to Delimiter = vbCrLf. I saved it as a xlsm file and it worked great. I got out of the spreadsheet and saved it. When I reopened to test it again, it would not work even though the same code was there. I have deleted the code and recopied it and also saved as a different xlsm file name. Do you have any ideas on what might have happened? I’m clueless and out of things to try. Any assistance would be appreciated.

    • Hi! You can use the ready-made code from the example file (Selections in separate lines sheet) linked to at the end of this article.

  4. Is it possible to create a drop down list in a blank spreadsheet without creating a table?

  5. Hi,
    thanks for this, very useful.
    I am getting a bug on the removal function.
    With using a simple example where I have on my list Green Apples, Oranges, Apples; after selecting "Apples" again, the macro removes the first "Apples" - so it returns: Green , Oranges, Apples - which is not what I am expecting.
    is there a way to fix this?
    Thanks in advance,

  6. Hello First thank you - I was trying to read the comments on how to make this work for all sheets in a workbook rather then copying duplicate code onto each sheet (if I read the instructions correct)

    • Hello!
      To run a macro on all worksheets in a workbook at once, the Workbook_SheetChange procedure is declared in the Workbook object and written in the ThisWorkbook code window. You can find the ThisWorkbook object in the VBAProject window below the worksheet list. Click on it and in the opened window add the code of the macro you need. You will need to add an additional parameter ByVal Sh As Object to the procedure.
      For example, instead of
      Sub Workbook_SheetChange(ByVal Destination As Range)
      write
      Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Destination As Range)

  7. I would like to use the "Multi-selection dropdown with item removal" but also allow for a write in on one option.
    For example: Location: Location A, Location B, Location C, Other Location:
    and the responder could click: "Location A" and "Other Location:", then go into the write in bar and type in "Garden" after "Other Location:"
    When I turn off the error alert in Data Validation I am able to do this but then the options start to duplicate themselves in the field: "Location A, Other Location:, Location A, Other Location: Garden"
    Thank you for your assistance

    • Hi! The macro assumes that all values in the cell will be entered from the drop-down list. When you are correcting a cell manually, all of its content is considered as a new value and added to the existing text in the cell. The result is duplication.
      If you have "Location A, Other Location:" written in a cell and you want to add "Garden". When editing the cell, delete all text, type "Garden", and press Enter. You will get "Location A, Other Location:,Garden".

  8. How do I adjust this code to work, in a single column instead of in multiple data validation lists?

    • Hi! I kindly ask you to take a closer look at the following paragraph of the article above: Multi-select dropdown for specific columns, rows, cells

  9. This code would prove to be very useful for me, but for some reason doesn't work for me? I downloaded the example workbook, but unfortunately all the drop downs just revert to normal - only allow single selection as per the list validation.

    I am guessing there is a setting in my excel that needs correcting or perhaps the version i am using isn't compatible?

    Any advice would be appreciated.

  10. Great solution Alexander. Very useful. Well done !

  11. Hi,
    Would be of a help if there is a VBA macro to auto map data validation list map basis the column header. Say for example for columns D to L with header name, I have the list of values defined with the same header name from column P to X. Data validation is to be set for column D basis column P, column E basis column Q and so on basis the matching column names. The header range where the data validation and the header range where the list values provided should be user selection as this may vary basis the requirement.

    • Hi! If I understand correctly, a special macro is required for your purposes. We offer a universal solution, where you can specify all the columns in which the macro should run.

  12. Is there a way to have the "multiple selection drop down list with item removal" appear in the same workbook but different worksheet than the key from which you want to pull content? Also, how does the VBA code know where to pull the key content from. For example, I started on the "key worksheet" but cannot find how to tell the code to pull list items only from let's say "Column B" and then create a multi list in Column D in another worksheet (titled "Master").
    I know if I add code: If Not Destination.Column = 4 Then GoTo exitError
    it should put content in column 4 but presumably it will be the worksheet I am creating the code for (my "key" worksheet) instead of the "master" worksheet where I want to have the drop downs.

    Next, and the biggest question, how do I run or enact the macro so it starts working? I have entered the code but see no changes in my drop down lists. They are single drop downs which I created prior to adding in this code.

    • Hi! Read the article carefully. It has all the explanations you need. You can create a drop-down list in any of the ways described, for example, in these articles: Create drop down list in Excel: static, dynamic, editable, searchable or How to make a dependent (cascading) drop-down list in Excel.
      The VBA code in this article does not create any drop-down list. It does allow a drop-down list to add multiple values to a cell. To do so, it must be placed on the same sheet as the drop-down list. There are additional settings to make the macro work only on individual rows, columns, or cells. You can read about that in the article above.
      The code you wrote allows the macro to work only in column D. It doesn't put anything anywhere else.
      I hope I answered your question.

  13. Great post.
    I found 1 issue, when the worksheet is protected.
    I applied your instructions for the following

    To restore protection, at the end of the code, before this line:
    exitError:

    Add this code:
    ActiveSheet.Protect Password:="password"

    This will unlock the worksheet on any list box that is single select.

    A list box defined as multi-select works as expected with this added following the protection additions
    If Destination.Address "$D$15" And Destination.Address "$C$86" And Destination.Address "$C$87" Then GoTo exitError

    I moved the ActiveSheet.Protect Password:="password" to right after Application.EnableEvents = True and now it no longer unlocks the worksheet for single list cells.

      • Hi Alexander - I needed to use the fix provided by Chris Strabley as well. Suggest reviewing your provided code to remedy the sheet unlock when single validation cells selected.

        Great code by the way, helped me out significantly.

        • Hi! We won't change the macro code for these suggestions, as most users don't need to. Also pay attention to the following paragraph: Enable multi-selection functionality in protected sheet. You can change the code for yourself, remembering about the security risk.

  14. Thank you for this. I am running into an issue that every time I add the code for a specific column If Not Destination.Column = 4 Then GoTo exitError on either the Multi item selection dropdown or or the multiselection dropdown with removal. The multiselection doesn't work any more... I am adding it after:

    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

    Thoughts?

    Thanks,

    • NVM. I figured it out.

    • Hello! With this code, multiple selection only works in column D. What exactly does not work for you? Describe it in more detail.

  15. Thank you for making me look smart in my meetings!'

  16. Sorting a worksheet column after adding this code does not work properly. Is there a way around this issue? List items do not appear in the column sort drop down list, When they do appear the list the requested sort list item does not return only the requested item. Thoughts?

    • I don't quite understand your problem. The multiple selection code only affects the operation of the drop-down list and nothing else. It creates a text string of multiple dropdown list items in a cell. I could not find a problem with the sorting. Describe your problem in more detail.

  17. Hi,
    Hope my message finds you well.
    I maked an excel file with VBA which works perfectly but stop working on others computer when i share it by eamil.
    How can i solve this issue please

  18. Hi Alexander Trifuntov (Ablebits Team)
    I want to know if there is a solution to use INDIRECT function on a multiple selected dropdonw list.

  19. You can change the VBA code and specify the column number in which the multiple selections will be made.
    After code:
    If rngDropdown Is Nothing Then GoTo exitError
    add code:
    If Not Destination.Column = 4 Then GoTo exitError

    Thanks
    But what if my column if more than one like column 15 and 16 and 19

    • Just below that example in the article there is an answer to your question.

      If Destination.Column <> 15 And Destination.Column <> 16 And Destination.Column <> 19 Then GoTo exitError

      • Infinitely Thanks.
        I realized you already solve it in "Multi-select dropdown for specific columns, rows, cells" section

  20. Excellent code, thank you!

    I need to have both multi-select drop down related VBA procedures you kindly allowed in your website work for one sheet because I have some columns here i want multi-select picks to allow duplicates and some columns to allow multi-select picks to NOT allow duplicates. Do i paste both procedures codes into the same code window? Do i need a call statement above the 2 procedures to call the one or the other depending on what column i am working in

    thank you!
    Craig

      • thanks very much for your prompt reply!

        Is you have any advice on where I can go to get some columns to allow multi select and dupes and some to not allow multi select dupes to work on same sheet, I would love to hear it!

        Craig

        • Hi! Pay attention to the following paragraph of the article above: Multi-select dropdown for specific columns, rows, cells. I hope it’ll be helpful.

  21. It was very useful, thanks a lot.

  22. Hello,

    When I pasted the code to different sheets, there is bug at '' If Destination.Count > 1 Then Exit Sub''.
    Could you please kindly explain to me what might be the reason behind?

    Best and thanks!

  23. Hi, is it possible to achieve similar "multi select drop down list" functionality in Office Script in Excel for WEB?
    or perhaps as an add-in for Excel for web?

    • I already answered this question in the comments. Unfortunately, Visual Basic only works in the desktop version of Microsoft Office.

  24. I am trying to use this but the code is not running. I have the selections stored in a separate sheet in the same workbook, which, for other users, you have said should not change anything. I pasted the code in the worksheet-specific module and saved it, but the code does not work in the sheet. What might be the problem with this?

  25. You are an absolute STAR, Alexander. THANK YOU

  26. I have encountered a complie error while I was using . May I know what's wrong? :

    Ambiguous name detected: Worksheet_SelectionChange

    • Hi! The ambiguous name error usually means that you have two subs within a module of the same name. Make sure you have copied the macro code correctly. You can also use the example file linked at the end of this article.

  27. I have used the code on my sheet and it works beautifully. However, is it possible to create a pivot table showing the total count of the individuals choices from the list?

    When I try to create a pivot table, it is not separating the individual choices.

  28. Hello,
    I'm trying to apply that code on my side, but I'm able to select sinlge item from a drop-down menu only. Probably, this is caused by the fact that source drop-down items are stored in another sheet. How could I overcome this?

    • Hi! Install the code for the macro you want on the sheet where the drop-down list is located. Source drop-down items can be on another sheet. Follow the instructions in the article above. If that doesn't help, describe your steps in detail.

      • Thank you very much for prompt response. I'll describe detilas after weekend

        • Hi again, the problem is resolved now. The reason was: two separate 'Private Sub Worksheet_Change(ByVal Target As Range)' procedures for the same sheet. After combining them into a single procedure, everything works as expected.
          Thank you!

  29. Thank you so much for the code! I've tried multiple codes from different websites to select multiple options from a drop down menu but this is the first one that actually works for me! Cheers :)

  30. Thank you for this article. I was able to leverage it to created a data validation with the ability to select multiple items. However, when I open it in 365, it will only let me select one item from the list. I have to open it in Desktop App to be able to select multiple items. Did I miss something or does it only work that way?

    Thank you!

    • Hi! It has already been noted in the comment below that the browser-based version of Excel Office 365 does not support VBA. Therefore, macros only work in the desktop version.

  31. HI

    great article and was very helpful. just wondering if it is possible to put a filter on that will find and select just a single item in the dropdown. for example (using your scenario) if i multiple dishes (lets say 30) and in them i had various ingredients added to the multiple drop down. but i only wanted to search for the dishes that had tomato in them. is there a way to just filter them? at the moment it is only letting me filter by what is in the dropdown of each row.

    hope that makes sense.

    regards

    • Hi! The macro is used in the usual standard Excel drop-down list, in which no filters can be used. You can manage the content of the named range that is used in the drop-down list. If this is not what you wanted, please describe your question in more detail.

      • HI
        How to write a code to print all the dropdown list values when we click any one value in that dropdown all the values must be printed in that dropdown list in excel

  32. Very useful; thanks. Do you have a version that works in an O365 browser-based instance of Excel?

    • Hi! Unfortunately, the browser-based version of Excel Office 365 does not support VBA. Therefore, custom VBA functions and macros are not possible there.

  33. Hi. This code works great but for some reason I get two drowndown arrows. I'm only able to select on the list if I click first the cell. Otherwise, the second black arrow is static and when clicked, it shows an empty list. This cause another problem when all entries are deleted within the list. After deletion, it won't allow me to select the cell using mouse click. I have to select the cell below it first and use the curson up key on my keyboard to be able to select the cell again and activate the drop-down list. Any clue why this is happening?

    • Hi! This code works with the usual standard Excel drop-down list. Unfortunately, I have not been able to reproduce your problem in my workbook. Try changing the height and width of the dropdown cell.

  34. This code works perfectly but has one problem when you have the list with similar values.
    ex. Semi-Automated, Automated
    It checks string if its contains not exact match in the list

    For that purpose need such condition:

    If Not IsError(Application.Match(newValue, Split(oldValue, ","), 0)) = 0 Then
    Target.Value = oldValue & ", " & newValue
    Else:
    Target.Value = oldValue
    End If

    • Hi! Please explain what you see as the problem. Similar values are different values. And naturally, the list values should not have signs that are used as delimiters.

      • Alexander , thank you for your comment!

        By similar values I mean the values that have same words.

        example: Red Apple, Apple

        So, this algorithm, in case uncheck item 'Apple' will find that old value: 'Red Apple, Apple' contains 'Apple',
        as a result I get 'Red'

        • Finally I came to this

          If Oldvalue = "" Then
          Target.Value = Newvalue
          Else
          Dim arr As Variant
          arr = Split(Oldvalue, ";")
          Dim str As String

          If Not IsError(Application.Match(Newvalue, arr, 0)) = 0 Then
          Target.Value = Oldvalue & ";" & Newvalue
          Else:
          Target.Value = Join(Filter(arr, Newvalue, False), ";")
          End If

        • If you don't use a character that is a separator in the values, there are no problems. The default delimiter is a comma.
          If you need a comma in the values, use a different delimiter as recommended in the article above.

          • In my case, problem is not in the delimiter but in the list that has complex words:
            Red Apple, White Apple, Apple

            Your code just replaces all words 'Apple', instead of using match comparison

            ElseIf InStr(1, oldValue, DelimiterType & newValue) Then
            oldValue = Replace(oldValue, newValue, "") ' removes an existing value from the list when you select it again
            Destination.Value = oldValue

            in my task I use:

            If Not IsError(Application.Match(newValue, oldArray, 0)) = 0 Then
            ..
            else
            For Each Item In oldArray
            If Item "" And Item newValue Then
            If UBound(newArray) >= 0 Then
            newArray(UBound(newArray)) = Item
            ReDim Preserve newArray(0 To UBound(newArray) + 1)
            End If
            End If
            Next Item

  35. Hello, the above CODE 'Multi-selection dropdown with item removal', works excellent, however it turns on the rule for all my dropdowns in the excel. how do i limit this code only to cell D5?

      • This is like Magic! Works perfectly, thank you so much!!!!!

      • Hello,

        I have a similar problem. I want the code to work only in column F, however, when I change the number from 4 to 6 it work in column E. Any reason for this?

        Thanks,

        • Hi! Unfortunately, I could not reproduce your problem in my workbook. Check if you put the code in the right line:
          If Not Destination.Column = 6 Then GoTo exitError
          Which columns does it work in?

    • Good Morning -
      This works great! BUT,.....I would like to be able to select multiple items at one time from the drop-down list rather than returning to the dropdown selector each time. I need to remove the repetitive steps with each item selected. Is there a way to index the selected items so the user can begin typing the first few letters and the cursor move to the entry in the table?
      Thank you for this great tutorial!

  36. Very clear, thanks! One question though, is it possible to order the answers automatically?

    For instance, if you have a dropdown with values: 1, 2 and 3. Now when you first select 2 and then select 1, the order is 2, 1. I would like to have it automatically change to order 1, 2, based on the order of the initial list of values. Regardless of the selection order.

  37. Hi! I'm trying to use the 'Item Removal' code, but my drop down is on a separate sheet than my list... Can you help me with modifying the code for this?

    • Hi! I think there's no problem placing the code on the same worksheet as the drop-down list. Follow the instructions in the article above.

      • Hi! Yes, the code works properly that way (as it does in your workbook file).

        What I'm trying to do is put my dropdown choices on Worksheet A, referencing the list and VBA code associated with worksheet B. The reason I'm doing this is because I need multiple different drop downs on 'Worksheet A'. Wondering if I can make that work...

        • Hi! Unfortunately, this VBA code does not have the ability to use several types of drop-down list with multiple choices on one sheet.

    • Hi Liz,

      The current code completely covers your case! Simply, create your dropdown as described in how to make dropdown from another worksheet. And then, add the code to the sheet where your dropdown is located. In the sample worksheet that is available for download at the end of the post, the source items and the 'dropdown with removal' are on different sheets, and all works beautifully.

  38. Is there a way to use COUNTIF, for example, to calculate how many dishes contain cheese? When I've tried, COUNTIF doesn't calculate for cells that contain more than 1 selection.

  39. Good and complete explanation
    It saved me.
    Thanks

  40. I love that I can make multiple selections from a drop down.
    The problem I am having is that when I save the workbook, using .xlsm, close the workbook, then go back to it, the multiple selection option is no longer working.
    What am I doing wrong?
    Thanks,

  41. Why doesn't the multi-select drop-down work when the sheet is protected, but works when the sheet is unprotected? I have made sure those cells are not locked in the sheet so the row formats can be expanded if more than one item is selected. How do I fix this problem?

    • Hi!
      If a worksheet is protected, by default, all cells are locked, even those that were previously unlocked. This means that any VBA code that tries to reference or access unlocked cells will not work when the worksheet is protected.

      In order to access unlocked cells that have validation rules applied, you will need to unprotect the worksheet first, execute the code that references the unlocked cells, and then protect the worksheet again.

  42. How do you make it so that it targets a specific column?

    • You can change the VBA code and specify the column number in which the multiple selections will be made.
      After code:
      If rngDropdown Is Nothing Then GoTo exitError

      add code:
      If Not Destination.Column = 4 Then GoTo exitError

      In this case, 4 means column D. In the other columns, the dropdown list will work as usual.
      Thanks to K N for clarification!

  43. Brilliant article. Well written, customer-oriented. You made it very easy for us :)

  44. When I share the sheet with Co-workers, they are not able to select multiple items. It reverts back to a single selection. But when I am in the document I can select multiple.

  45. Within the same sheet where multi-selection code is installed, is there a way to specify which drop-down use multi-selection and which do not?

    • If say for example you have lists in A, B & C and only want to apply this multi-select to those in column B, simply add the code below before the On Error Resume Next line.

      If Not Destination.Column = 2 Then Exit Sub

  46. Excellent examples and well written. I now have a ISO 27001 SOA that has lots of dropdowns to choose from when updating rather than cut n paste. Brilliant, thank you, Toby

  47. Hi, I get a data validation error when I choose more than one item from the list as the Data type is listed as "list". Is there a way to solve this without having to click "ignore error" every time?

    Thanks.

    • Hi! In the second screenshot in the article above, you can see that the data type should be specified as "list". So I can't understand the reason for your error.

      • I'm getting a similar error with the code for "Multi-selection dropdown with item removal". If I only select one value from the list, no validation error. But if I select more than one option, I get the error. It's as if the code is trying to validate the entire contents of the destination cell as a single combined value, instead of separate delimited values within the cell.

        • Hi! I tested the macro using the sample file linked at the end of this article. I did not get an error. Check if you copied the code correctly. Describe in detail what you do when you get an error.

          • Hello, I have the same issue. But not when I use the sample file. I found the reason why. I'm using the code in a table, while in your sample file this is applied to a range. Once I converted my table to range, it works without errors.

            Hope this helps ! Cheers, Christophe

          • Getting this same issue. Multi-selection dropdown with item removal. If I only select one value from the list, no error however, when I selection 2 or more options, I receive the data validation error. I copied the code exactly as it is above

            • Hi! You can check how the code works in the sample file. The link is at the end of the article. Follow all the instructions correctly.

  48. Can the number of items selected be limited to a selected number (i.e. Maximum of 5 selections)?

  49. The code to 'Multi-selection dropdown with item removal' seems to have a problem removing the last item selected.

    For Example:

    1. Choose three items from pull-down list
    2. Deselect first item, deselect second item
    3. The third item can't be deselected. It's like the cell is required to have at least one item.

    Can the VBA be modified to allow all items to be deselected?

  50. Many thanks, Clear explanations and the code is working perfectly.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)