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 4. Total comments: 508

  1. Hello, thank you for this useful website.

    I have questions please,
    I tried making a drop down with multiple selections for my data, but when I insert the formula =INDIRECT("Table1[Items]")
    I get an error message saying (the source currently evaluate to error !)
    I don't why?

    Another question: I cannot see from where the name "Table1" in the above formula came?

    Thank you

  2. I am not an expert in excel. Your VBA code helped me a lot. But still am facing problem with the "protection".

    Can u explain where to insert the protection code along with the existing code.

    I did copy, paste with the existing code, but it doesn't work. do you have some pictures or videos to share.

    Thank you.

    1. Hi! You can see a sample code in this paragraph above: How to enable multi-selection functionality in protected sheet.

      1. Hi,

        I've tried to the copy paste of the code that was recommended in the paragraph above, but it didn't work. I saw other people had the some issue. Could you please give an example of the complete code including both the Multi Selection functionality as well as the code to Unprotect and protect so we could evaluate what we're not doing properly?
        Or even include this type of example in the excel spreadsheet you provided as an example?

        Thank you in advance

        1. Hi!
          1. Add the code you need to the worksheet. For example, Multi-select drop down without duplicates.
          2. AFTER THIS CODE, add the code for working on the protected sheet from the paragraph: Enable multi-selection functionality in protected sheet.
          3. That's all.

  3. The multi-select code with and without removal does not work. I have read and implemented the instructions 6 times and only one item can be selected. I have saved the sheet as a macro enabled excel file. I have created the list using the table and the "INDIRECT" line of code. I have created the developer tab and added the VBA code exactly as it is shown in the article. But only one item can be selected.

    1. You may want to add a step to your list. I had to close out of the spreadsheet entirely and re-open it for the macro to be enabled.

      This code is a tremendous help. Thanks!

  4. Hello,

    Thanks so much for this code - it's great! One challenge I have though is that when you use the code to enable removal of previously selected items it doesn't quite work for me. Let's say I have 2 items that can be selected - QWE; RTY and am using the custom delimiter: " | "

    If I select QWE, then RTY, they are now both added to the list like this : "QWE | RTY"
    If I re-select RTY, it is now removed from the list like this: "QWE"
    If I re-select QWE a second time, it is added to the list a second time, NOT removed, like this "QWE | QWE"
    If I re-select QWE again a third time, they are both now removed and there is nothing left in the list.

    The case where an item is added twice only happens when you try and remove the first item in the list. My best guess is that this because the first instance automatically doesn't have a de-limiter, so perhaps the code for removal is looking for a de-limiter, doesn't see it, then s then adds the item again with one? Then when you select a third time, it picks them both up due to the de-limiter?

    I'm not sure, I'm not great with code but would appreciate if you're able to provide any other advice on this. this is the only VB I have in this document.

    Thanks!

    1. Hi! We have made changes in the code. Reinstall the code Multi-selection dropdown with item removal. Thank you for your comment!

  5. Hi,
    code was working greatly, but I have now new company notebook, suddenly code stopped working. I have downloaded your practice sheet, and it does not work as well.
    No errors, dropdown list allows only one selection.
    Excel version: Microsoft® Excel® for Microsoft 365 MSO (Version 2310 Build 16.0.16924.20054) 64-bit
    Any suggestions ?

      1. Hi.
        I understand the basics for macros to run.
        OFF COURSE all macros are enabled, folders trusted.

        Anyway it doesnt work. I was trying altering the code.
        I deleted this section at the end and it worked for a while. Then again stopped working.
        Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        End Sub

        Looks to me like option explicit doesnt run properly.

        1. Hi. On the old computer the code worked, on the new computer it does not work. Conclusion: your new computer software is the problem, not the macro code. Unfortunately, I am not able to solve this problem for you.

  6. Hi Thank you for this code - it works but I wanted to take it a step further. I am using the code which places the selected items on a new line (so DelimiterType = vbCrLf), but I also want to add a comma to the previous line.

    I have tried "DelimiterType = & ", " vbCrLf" - but this creates an issue because it allows an item to be selected twice, then after selecting that item a couple more times, only then do all instances get deleted.

    Can you advise how to add a comma to the end of the previous line?

    Thank you.

    Gemma

    2. Actually, I've just recopied the code and used "DelimiterType = "," & vbCrLf" and it seems to be working now.

      Thank you

      Gemma

  7. Hi, thank you this code was very helpful. I am using the code which allows duplicates to be selected and I would like to count how many times each word is mentioned (including duplicates within cells). Do you know if there is a way to do this? I have tried the COUNTIF and SUMPRODUCT functions but cannot find a way to count the duplicates also - it counts a maximum of 1 per cell. Thanks.

  8. Hi. Thanks for the comprehensive explanation. I have used the VBA successfully and created the multi selection list for all my worksheets - it works great! Once i have collected my data, is there any way to calculate/summarise how many of each selection within a drop down list have been selected. i.e. if i and counting fruit and have selected "apples" ten times (or 1000) and selected "pears" 37 times, how can i get excel to calculate that without me having to count each individual selection.

  9. Hi Alexander I have your code working, but as soon as I close and reopen the file, the code won't work anymore, do you have any suggestions for me please? I check and nothing has changed, I have copied it to a new workbook and the exact same thing happens, it works until I close, then when I reopen, it will only let me select one item per cell. I did not add specified columns to the code, and my worksheet has about 10 list tables, so not sure if it's too much information? I am also very new to VB, so I could be doing something without realising I'm erring. Data tables are on a separate worksheet and the code is only on the worksheet I have the data validation on.

  10. Hi!

    Thanks for sharing this. I tried the code, and it works for examples given above.

    However, I would like to use the multiple selected items as input for a function (e.g. FILTER function).

    Let's say I have a dataset for several years, and want to do a report with the filter function, filtering data for selected years, but giving the user the ability to select one or more years from a dropdown list.

    Is there a way to do this?

    Thank you in advance for your help. :)

    1. Hi! The macro allows you to create a text string of multiple items in a single cell. But I cannot advise you how to use this text in the FILTER function. Each criterion of the FILTER function must be written separately.

  11. Hi - thanks for the code. When I use this I get the multi-select list working fine - however Excel is showing the standard 'data validation' error on the cell, as a concatenated version of selections isn't valid according to the original list defined in the data validation tab.
    Is there any way to get Excel to not show up the issue?
    Further, I cant use the formula bar to edit the result (i.e. manually delete an entry selected), because again, it's not valid based on the original formula list provided.
    Any help would be appreciated!

    1. Also, the password protection seems to be a bit funny! The code seems to turn protection on before I have, and then the list doesn't work anymore?

      1. I fixed the first by protecting the sheet - and the second I used different code.
        Sub Example()
        ActiveSheet.Unprotect
        Program logic...
        ActiveSheet.Protect
        End Sub

        Around the sub I copied. Thanks :)

  12. first of all thank you very much, The code is really good and works great ! ?
    I saw that people wrote that they have a problem when the sheet is protected. I also had a problem exactly like this, that the multipicklist worked fine, but as soon as I make the sheet protected, the multipicklist becomes a normal picklist. And even when I added your code for the protect it didn't help. I investigated your sample file which continues to work fine even when the sheet is protected, And I tried to understand what is the difference between us. I think that I came to a conclusion as to what is causing the problem:
    When a different cell with a different validation is defined in the sheet, it prevents the multipicklist from working when the sheet is protected. I you will add to your sheet another validation in other cell, you will probably see the problem.
    I really don't understand VBA, I would appreciate if you have an idea what can be changed in the code so that it works even when there are several different validations in the sheet.
    Thank you very much !!

    1. Hi! If I understood the problem correctly, you can only use one macro per worksheet. It is written about it in the article. All macro variants are in the article. You can add the code for the protected worksheet to any macro, as described above. I hope I have answered your question.

      1. Thanks for the replay.
        I m using only one macro on the sheet.
        But I have two cells with different validations. Only one of them needs to be a multipicklist and uses your macro, the other validation is a number validation and doesn't have a list at all.
        When I protect the sheet and add your code for protecting, the multipicklist becomes a regular picklist. It doesn't run the code correctly.
        I tried to debug (As I said before, I don't know VBA, but I am programming in other languages). when I select a value from the list, I see that on the line "If rngDropdown Is Nothing Then GoTo exitError " it falls and goes to exitError without doing all the other important lines.
        I would appreciate your help please. Thanks.

        1. Hi! I downloaded the sample file at the end of the article and added the code for the protected sheet. I did not get any errors. Try reinstalling the macro code.

          1. Your code works great, I didn't say it didn't.
            But I meant that if you add another validation in another cell (like a validation on a number) and then you will see the problem.
            In any case, my problem was solved in another way, the client asked to remove his field from the file ?.

            1. As I said, I added another validation in another cell (validation on a number) but I didn't see any problems. There is some problem in your file and maybe reinstalling the code would solve it.

              1. The code works great on protected sheets if i use the codes on your sample sheet, but if I modify the code to make specific columns have the multi-selection option, the code not work and turn to single selection only.
                Please you need to change the code on your sample sheet and add this line code for example (If Destination.Column 16 And Destination.Column 17 And Destination.Column 18 And Destination.Column 19 And Destination.Column 20 And Destination.Column 21 And Destination.Column 22 And Destination.Column 23 And Destination.Column 24 And Destination.Column 29 Then GoTo exitError) to see the problem.

  13. Hi,

    Amazing work. I have successfully implemented the code. However, when I transfer the file through e-mail, and then download it again, the multi-selection dropdown doesn't seem to work any more. Would you happen to know a solution to this problem?

    1. I have resolved this issue, it was due to the excel being a read-only file. I saved it as another file and it worked.
      Thanks again.

  14. Thank you for this code. It has been really helpful. If I want to be able to scroll through the drop down menu rather than using the arrow buttons to go through the list, how would I do that?

  15. This worked perfectly when I first tried it out, many thanks! However, when I got an update of MS Office, the code does not work for 64 bit - any way this could be converted easily?

    Thanks!

      1. I'll try, thanks!

  16. Thank you for your help! I inserted the primary code into my spreadsheet and it worked as described. However, when I tried to add in the additional code to enable a multi-select dropdown functionality in a protected worksheet, I would get the following error: Compile error: Ambiguous name detected: Worksheet_SelectionChange
    It would specifically highlight the first line of the added code which followed the primary code. I tried both protecting the sheet and workbook individually and together, and each time I would get the error. The last group in the primary code has the same "Private Sub Worksheet_SelectionChange (ByVal Target As Range)" language. I tried replacing that with the additional code, but that only resulted in the multi-select dropdown no longer functional. I have updated the password options as instructed.
    I don't see this in the practice workbook, and I'm not sure how to fix. Any help you can provide would be much appreciated. Thanks!

    1. Hi! Check your code carefully. Private Sub Worksheet_Change occurs there twice. Delete the unnecessary code.

      1. Sorry, the primary code I used was for the "Multi-selection dropdown with item removal" option. So that only has one occurrence of "Private Sub Worksheet_Change". The ending version is "Private Sub Worksheet_SelectionChange". I combined this version of the primary code with the "multi-select dropdown functionality in a protected worksheet", leaving 2 occurrences of "Private Sub Worksheet_SelectionChange".

  17. I am trying to create a shared document for work using the Multi-selection dropdown with item removal and it works great however it doesn't cross over to the online version (which all my colleagues would see).

    It still lets me select from the list, the most basic form of it works, but the mutli selection and item removal doesn't seem to be translating over.

    Any idea how I can fix this?

  18. Hi, thanks for the code. Selecting multiple options works smoothly, but if by mistake I select a wrong item in the list, how can I remove it? nothing seems todo the trick. thanks in advance

    1. Hi! To remove the wrong item from a cell, you can use the code: Multi-selection dropdown with item removal. Or you can manually correct the data in the cell because the dropdown list creates a text string. You can edit it in the formula bar.

    2. my bad, apologies, I used the wrong code snippet. all works perfectly

  19. Hello,
    This code works beautifully, until I upload my spreadsheet to onedrive so that I can share it with colleagues, it then loses the option to select multiple options from the drop down list and only allows you to choose one, Is there a way to change the VBA code for a share point excel spreadsheet, Or is there another way to add a multiple selection list for one that opens on chrome ? Any help would be appreciated

    1. Hi! Remember that VBA only works in the desktop version of Excel. VBA macros and functions do not work in Excel Online.

  20. Hello,

    This is all great information!! So far is working perfectly! I do have a question, Is there any code that could eliminate your selection from the drop down box in the next row? For example, If I have multiple people using the excel spreadsheet and we have 2 rows with employee names in the drop down list, 1 person picks 3 names from the list and the next person in the next row needs to see what names they cannot pick.

  21. Thank you very much for providing this code, it works just how it's suppost to! I used the dropdown with multiple selections in separate lines code and realized, that the code does not keep the entries in the order of the drop-down menu. For instance: If you select Apricot and then Bread crumps, it will show it in the right sequence.If you de-select Apricot, it correctly only shows Bread crumps. However if you then select Apricot again, the result will be displayed at Bread crumps, Apricot. Is there a way to keep the selections in the order of the drop-down?

    1. Hello! Since the drop-down list creates a text string in the cell, you need to write special VBA code to sort the words in the text string within the cell.

  22. The drop down list with multi- selection is great.

    Is there any code that could eliminate your selection from the drop down box and repopulate it in the next cell over when it is clicked?

  23. Hello,

    Thanks so much for these codes. It works perfectly. Would it be possible to enhance the code to provide user with long description in the drop down list, but returns the short abbreviation in the worksheet? For example, when entering a state, user types in "A" and the drop down shows "Alaska, Alabama, Arizona, Arkansas". If user selects "Alaska", the worksheet would record "AL".

    Best Regards.

    1. Hi again,

      please ignore. I figured out how to accomplish what I needed. In the line "newValue = Destination.Value", I simply replaced it with "newValue = Left(Destination.Value, InStr(1, Destination.Value, "-") - 1)". Of course, the state list would have to be entered as "AK-Alaska"; "AL-Alabama", ... Drop down list would show "AL-Alabama", but when user selects it, worksheet will show "AL".

      Many many thanks for the codes.

  24. Hi
    Thanks for the code. It works perfectly and it saved a lot of our time.
    Just wondering if it is possible to have different text colour for each phrase in one cell.
    Let say in your example, whenever 'Milk' is selected, it shows with a red text colour and the rest item are shown black. Is this something possible?
    BTW, I already tried the cell rule but it will change the text colour for entire cell not one item.
    Many thanks

  25. First, thank you for sharing this code. I'm excited to see it in use with my current worksheet. However, I have a question first: where do we insert this code? You wrote, "insert the VBA code at the back end of the target worksheet" without details for what that means.

    1. Hi! Read the entire article. Instructions are provided below in this paragraph: Insert VBA code to allow multiple selections.

  26. Hi, thanks for our tutorial!
    May I know if I can filter those "multiple selections" data that we just created?

    1. Hi! The drop-down list with multiple selections creates a text string. To create a filter on a part of this text string, you can try using the advanced filter options as described in these instructions: Filter text data.

  27. Hi,

    Thank you for this valuable multiselection piece of VBA. Unfortunately, I have the same issue with an error flag on each cell now. The multi selection is working and even with your code enabled, there is no error flag if you select one option only. The error only comes when you select at least 2 options from the drop down list.

    I can't appy BThompson's suggestion as I only have 1 table.

    Any other suggestion please?

    Best regards,

    PS : I guess the filtering on this column with multiple options selected will not allow filtering by one of these options only, right? Any suggestion to allow "smart" filtering once multiple options are entered in a single cell?

    1. Hi! You said earlier that the sample file you downloaded works without errors. So, the problem is in your file. Check what VBA codes are in your file. Maybe some other code is preventing the macro from working. You may have characters in your data that you also use as delimiters. We discussed this in the comments above. I can't check your file.
      The drop-down list creates a text string. To create a filter on a part of a text string, you can try using the advanced filter options as described in this guide: Filter text data.

  28. Hello, I used your script and it works. However, the macros won't run for excel online. Do you have any idea how to fix it?

    1. Hi! I already answered in the comments above that Excel Online does not work with VBA. Therefore, macros and user-defined functions do not work there.

  29. This is great! One issue, I am now getting a data validation error flag on every cell where I have selected multiple items from the list. The field type information is - Data type: List Restriction: Value must match one of the listed items. Blanks are ignored and are automatically valid.

    I tried unchecking the 'show error alert after invalid data is entered' box in data validation but the error flags are still appearing.

    1. I ran into the same error. Try spacing your tables apart at least one column. Otherwise, your individual tables will be treated as part of the larger, original table.

      1. I have the same data validation issue. It seems isolated to when your data is in a table instead of just a collection of columns

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  40. 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).

  41. Is there a way to share the file with others that has multiple selection so that they can edit the sheet?

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

  42. 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"

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

  43. Now I got the problem that my other Formulars not work. How can I fix that I need them.

    1. The multiple selections macro only works in cells that have a drop-down list. It cannot affect your formulas.

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

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

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

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

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

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

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

    1. Hi! You have inserted the code incorrectly. You can use the sample file linked at the end of this article.

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

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

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

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

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

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 :)