Comments on: How to make Excel drop down list with multiple selections

By default, Excel data validation only allows users to select 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. Continue reading →

Comments page 6. Total comments: 552

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

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

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

    1. When I download the example sheet, the code that is already in that sheet does not work also.

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

    Ambiguous name detected: Worksheet_SelectionChange

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

  4. 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.

  5. 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!

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

  6. 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

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

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

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

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

  8. 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

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

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

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

  9. 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?

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

  10. 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.

    1. Hi! Sorting words in a cell is a separate task that must be handled independently from the multiple selection macro.

  11. 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?

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

  12. 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.

  13. 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,

  14. 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?

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

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

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

  16. 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.

  17. 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?

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

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

    1. Hi!
      We did not limit the user to the number of selected items, because they can have completely different tasks.

  19. 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?

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)