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 3. Total comments: 552

  1. Hello,

    Thank you for sharing this guidance and code.

    Is there a way for the COUNTIF function to pick up when an option has been selected amongst others? For example, on your scenario to pull a count for how many times egg and cheese were selected in total?

    Thanks
    Claire

  2. Thank you for this great tutorial!

    I am working on using it in a table with tasks where ressources are selected for each task. I would like to use the drop-down list for the ressource(s) for each task. But when I copy the cell with the list (or drag the fill handle), I only get the value in the next cell and not the list. IS it possible to copy the cell with the list to other cells and still keep til dropdown list in the new cell?

    I have used the vba code for "Multi-selection dropdown with item removal" with DelimiterType = vbCrLf.

    Thank you for your help!

  3. Hi, thank you so much for this important code and explanation. But I would like to know how to connect the multi-select drop down list (which includes 3 names for an example) automatically with another cells. Let's say that I have a database table includes names (column A) and salaries (column B), how can I make a multi-select drop down list for names (column C) and the next cell (column D) shows their salaries in the same sequence automatically?

  4. Fabulous - thank you!

    I implemented 'How to create dropdown with multiple selections in separate lines'
    and 'How to make a multiple selection dropdown with custom delimiter'

    Icing on the cake would be to allow fill-ins as well as multi-selections - can this be done?

  5. This is VERY helpful! Is it possible to alphabetize the list in the cell after each selection?

    1. Hello Krissi!
      In order to sort the words in a text in a cell that has been created using a drop-down list, you need to use a special VBA macro.

      1. Hi! Amazing post! Saved me a lot of time :)
        I just wanted to follow up on Krissi's comment. What would be the addition to the existing VBA macro that we inputted to create the multi-select dropdown so that the selections sort alphabetically and not in the order they were selected.

        Thank you so much!

        1. Hello Andres!
          To sort the result of a multi-select drop down list, you can add this code:

          Dim words() As String
          Dim sortedText As String
          Dim i1 As Integer, j1 As Integer
          Dim temp As String

          ' Split the text into words
          words = Split(Destination.Value, DelimiterType)

          ' Sorting words
          For i1 = LBound(words) To UBound(words) - 1
          For j1 = i1 + 1 To UBound(words)
          If words(i1) > words(j1) Then
          temp = words(i1)
          words(i1) = words(j1)
          words(j1) = temp
          End If
          Next j1
          Next i1

          ' Combining sorted words into a string
          sortedText = Join(words, DelimiterType)

          ' Write the sorted text back to the destination cell
          Destination.Value = sortedText

          Insert this code before the lines of code

          End If
          End If
          exitError:

          1. This works perfectly! Is there a way to apply a custom sorting rule instead of in alphabetical order?

            Say I wanted a list of names like Tim, Bob, Bill, Chelsea, Kora, and Xander. But I wanted Xander to always appear first if selected, then Kora, then Tim, Chelsea, and so on.

            Is there code I could insert to sort the selected data from a custom ordered list (maybe a column on another hidden row or sheet?)

            Thanks!

  6. Thank you for this! This is exactly what I needed and explained greatly, but a question:
    I now have the code so that the choices are seperated by ", " & vbCrLf So that it puts the words under each other in the table. But when I wanted to use the filter in the table it put all the words in the cell together. Is there a way to change the code so it reads every choice as a separate? Or maybe a different macro somewhere? I couldn't find it.
    Like a code that says that words in a certain colom are divided by a "," and the filter has to read the words separated by "," as different words. Even if they are in the same cell. So the filter shows all the cells (and rows in the table) that contain the 1 word I am filtering on.

    It seems do-able but I don't have the skill and knowledge yet by far to figure it out.

  7. Just like the other comments it works initially and then refuses to work after I save and close (opened the next day and multi select doesn't work) I've looked into the trust center and still nothing. Thoughts?

  8. Hi Alexander - This works great! Where multiple selections are set to go to the next line - is it also possible to add a custom symbol (ie. ;) after each response as well ... combining the new line option with the custom delimiter?

    1. Hi! If I understand you correctly, you can use multiple characters in the delimiter. For example:

      DelimiterType = ";" & vbCrLf

  9. It was successful at first. However, when I closed the file and reopened, the multiple selection no longer worked. When I tried to view the VBA code in the Code Window, I could not even open the Code Window. I am sure about saving it correctly as .xlsm file.

  10. Can checkbox come in the drop down list? it will be helpful

      1. I think it is possible - by combining data validation with ListBox (ActiveX) or ComboBox (ActiveX).

  11. Hey thanks for the code, its indeed helpful. But i just noticed that the options in target cells dont automatically appear as a dropdown list. Instead i am having to key in a letter and then the list displays all contents of the source list that contains the key word i typed. How can i have a drop down list with all items from the source list displayed for me to choose from?

    Many thanks in advance for the support. Much appreciated.

  12. This article has been a lifesaver! I do have a question about how I can have the macro applied to all worksheets in my workbook without having to manually add the macro each time I create a new worksheet? I have to make a new worksheet monthly and it would save a bit of time if I could have it apply automatically. Thanks

    1. Hello Brandy!
      The Private Sub Worksheet_Change event works only on one worksheet where the VBA code is written.

  13. does it work when the dropdown table/list is in a separate spreadsheet?

      1. Hello Alex,
        Thanks for getting back to me. is there any code that i can use to run Macro automatically when i open excel workbook? Thanks

      2. Thank you, I tried this code & it was brilliant. Since next day the code is not working Not sure why?

  14. Is there a way to add "and" before the last selection? ex: instead of "test 1, test 2, test 3" I'd like it to display "test 1, test 2, and test 3". Thanks!

  15. I am able to get the multi select to work for myself without any issue but when I share the workbook with colleagues, the multi select does not work and the information is simply replaced when the next item is selected.

  16. Hello! First let me say, thank you! These are very niche problems and I am grateful that someone has created solutions for them.

    I am having a couple of issues. First issue is that, with the code that includes removal functionality, the removal piece doesn't work for all items in the dropdown list. For the first few it works, but for item #4 and up, a second click just duplicates the selection. Can you help with this?

    Second thing is less of an issue and more of a request pertaining to the first issue -- is there a way to add the 'No Duplicates' bit of the code into the code for removal capabilities?

    Thanks in advance!!

    1. I will just clarify -- I would like to have removal capability AND to block duplicate selections for all dropdown items

  17. This is awesome thank you! Is there a way that you can create a counter for this? Using the example listed above, let's say all four rows need "Salt", am I able to create a counter table that shows how often the word "salt" is said in each of the four rows? I tried using a pivot table and it counts based on what each individual cell says, unable to separate the individual words

  18. Hi when I try to apply the code for a protected worksheet after the main code, the functionality of the multi-select is either taken away or there is an error - could you please provide some guidance other than the instruction above of exactly where the code needs to be added after the main code, and also if the sheet needs to be password protected firs before entering the VBA code for protected worksheets?

    1. Hello Adrie!
      In addition to the above instructions, the usage of VBA code for multiple selection on a protected sheet has been discussed in detail in the comments below.

  19. Hi Are we able to do multiple selection and also have dependent drop down in the next column please.

    For eg if I select milk and apple in the next column get the dependent dropdown semi/ no fat/ full fat as well as drop downs for apple

    1. Hello Nancy!
      Create a dependent dropdown list as described in this guide: How to make a dependent (cascading) drop-down list in Excel. With the help of the macro proposed in this article, you can organize multiple selection in any drop-down list, including the dependent drop-down list.
      You can only make multiple selections in the dependent drop-down list. If you select 2 values in the main drop-down list, the dependent drop-down list will not work.

  20. Hi, this is superb! Can you multi select from the dropdown. I have a need to select about 20 items from the drop down and I am having to click on dropdwon arrow and select one at a time.

  21. Hi!

    This worked exactly as described, used the option to be able to remove selections.

    ??? Question ???
    Any tips on adding in a sort to the back side of this so it always either Alphabetizes the list or orders it in the order of the drop down source list?

    1. Hi! A drop-down list creates a text string in a cell. To sort this text string, you need a special macro.

  22. Good day
    I have a task and it wants me to paste a table from one file into another separate excel file by using a dialogue box with macros by getting the code from excel .However the code keeps giving the error "debug".Could you please help me .

  23. I don't understand where to put the code for the protected worksheet. When I add it nothing else works or the multi-drop down doesn't work. Can you please tell me where to add it in with the Multi-selection dropdown with item removal.
    Thanks

    1. Hi! All the necessary steps on how to insert code into a protected Excel worksheet are described in detail above.

  24. Hello! Have you ever tried to create a slicer of the drop down data? Does it work? If so, how? If not, then is there a way to do it?

  25. I am not able to apply the code suggested to make the multi selection functionality work when a sheet is protected. If I cut and paste the code at the bottom of the code I copied from the multi-selection without duplicates option and try to go to use my drop down list, I receive the following error: Compile error. Ambiguous name detected: Worksheet_SelectionChange

    2. I forgot to mention that I added the specific cell reference code to the original code because I have multiple data validation lists in a sheet and only wanted the multi selection applied to one of them.

  26. The codes are great, however the multi selection option didn't work when you protect the sheet and allow multi selection cells for editing.
    Any help on that?

    1. Hi! I can't check your worksheet and I can't check your VBA code. However, this is a question that has been asked many times in the past. You may be able to find the answer in the comments section below.

  27. This code is great! I'm wondering if there's a way to provide a default text display that goes away when something on the list is selected.

  28. Thanks for this. It worked great. However, when i shared the file it no longer worked. But it still works on the original file. Any thoughts? I am trying to share a Onedrive link for others to collaborate on the same file.

  29. I'm not sure this can be done but, I'm trying to create a multiple entry dropdown box, but for each entry I select I want to be able to select specific information from another multiple entry dropdown box. For example, I am trying to identify a list of claims in the first cell, but as I enter each selection, I want to be able to "attach" multiple bases to that claim from the cell next it.

    Like Cell D2 my first selection is CONSTRUCTIVE DISCHARGE. When that selection is entered, I want to be able to select from Cell E2 bases like AGE, DISABILITY and NATIONAL ORIGIN.
    Then, I would like to make a second entry in Cell D2 like DISCIPLINARY ACTION. And when that is entered I need to select bases from E2 like: DISABILITY and RACE

    Right now, I have to list one Claim in D2 then add the Bases in E2, then drop down to D3 and enter another Claim and then enter the Bases for that claim into E3. That requires me to merge all of the other cells once I am finished entering the Claims and Bases, so that each claim and corresponding bases are captured under the same case number. But that's a pain because once I start merging cells, I can't sort the entire worksheet.

    It's probably impossible to do. Or maybe I need to use a combination of dropdown lists and picklists. I don't know. Any advice would be appreciated.

    Thanks

    1. Thank you, Alex. I'll look at the Dynamic Dependent Dropdown Box link and I will probably have to go out and get a VBA code for Dummies book so I can learn ho to do this. It's kind of funny that I'm going through all of this time and effort to create a function on my spreadsheet that is designed to keep me from expending excess time and effort when entering data. But, I'm stubborn that way and it will be worth it in the end.

      Thanks again for your quick response and assistance.

  30. Thanks for this detailed explanation. I was wondering if it would be possible to "connect" a selection to another cell - here are the details:
    I want to have a multiple choice dropdown in column D (this works) and a multiple choice dropdown in column E (works as well). Now, if column D indicates the profession (e.g. doctor), I want to have a multiple choice dropdown in column E (indicating area of expertise) that just shows me the choices of areas a doctor might have expertise in (e.g. immunology, cancer).
    Does this seem feasible? If yes, how would one accomplish this?
    Thanks in advance.

  31. Hi,

    I have inserted the code and it works perfectly fine for me and for several other colleagues but for some of them it doesn't. The file is stored in a Teams folder but always opened in the desktop app. Any idea on what the reason for this could be? Could this be settings related on their end? Pleased to learn and thanks in advance!

    Toon

  32. Hello!

    Great code! I was wondering if there's a way to replace values in the dropdown selection after selections have been made. For example, if I have in a cell: "Bread crumbs, Celery, Onion," I want to change "Bread crumbs" to "Crumbs" in the source sheet and see the same change reflected in the dropdown without having to clear the dropdown and reselect all values again. Is there a way to do this?

    Regards - Otto

    1. Hi! A drop-down list creates a text string that cannot dynamically change depending on the values of other cells.

  33. Hello! When attempting to use this code I have been getting "Run Time Error '50290': Method 'EnableEvents' of object '_Application' failed" and then when I click debug it highlights the "exitError: Application.EnableEvents = True" line. What would be the solve here? I copy and pasted the code exactly into VBA, so I can't figure out where it's going wrong.

    1. Hi! Try to insert the code correctly again. You can also insert the code from the sample file linked at the end of the article.

  34. Hi! This works great - but not in the web version of excel in office 365 / sharepoint. Is there a way to enable this for web based excel?

  35. Hello, thanks for this detailed explanation. It is very helpful. I am using the VBA code to select multiple items in dropdown list. I was wondering if there is a way to count how many times each word/slection is used. I tried using the =COUNTIF but as soon as a word/slection is repeaded from the dropdown list, it brings the count to 0.

  36. Hello, thantks for the content, is very well written and the code works perfectly.
    I have one question, is it possible to filter those informations inividually? Because by using your code I've noticed that the informations are classified as one new variable, but I would like to obtain a filter in the colum that would get for me all the lines that have "eggs" for example. How can I do it?

    Thanks

      1. Hi Alexander,

        Thanks a lot for this helpful comment. Can you please expand on what you mean by partial match? I checked the article suggested and couldn't find the partial match you suggested.

        Thanks
        Oudai

  37. Hello! This isn't working for me... Does the data validation have to be in the same sheet as the drop down menu? I used the additional code: "If Not Destination.Column = 4 Then GoTo exitError" in the correct sheet and column but it won't apply to the drop down menu.

    1. Hello! Unfortunately, I have no way of knowing what you did or how you did the code installation. The code should be on the same sheet as the drop-down list. Follow the instructions carefully, or you can download the sample file at the end of the article.

  38. Sorry. I have one more question. Can I use two different codes on the same sheet (i.e., multiple selection dropdown with removal for cells C:10:D50 AND a multiple selection dropdown with repetition for cells H10:H50). I can get the first own working, but when I drop the code for the second one, nothing works. I know it's in the code but I just don't know how to fix it. Thanks

  39. I'm sorry, but I have another question. How can I combine 2 of these VBA codes together so that I can have a certain set of cells (i.e., D3:F15) as multiple selection with removal and then another set of (H3:H55) as multiple selection without the removal function?

      1. That is unfortunate. But at least now I know it's not something I was doing wrong that kept it from doing what I wanted it to do. Thanks again for these codes. With the exception of the extra things I want, they do exactly what I need.

  40. This is great. Is it possible to number each item in a multiple dropdown list AND be able to preserve that number if you accidentally selected an item and then had to remove it?

      1. Fair enough. At least your response gives me hope that it can be done. Going to the bookstore now to get Visual Basic for Dummies.

  41. Hi - I am using the code for 'Multi-selection dropdown with item removal' and its working correctly across the whole sheet. I am trying to narrow it down to only a few columns. I have tried to insert the code "If Destination.Column 4 And Destination.Column 6 Then GoTo exitError" and it has not worked.

    Is there a specific spot to insert this code? The instructions are not clear to me where it should be entered. "Add immediately after it, add a new line specifying where to allow multiple selections, as explained in the below examples."

    Thanks for your help!

    1. please ignore me - worked it out.. my mistake!

  42. Hi,

    Great article!

    Id like another cell to tell me the number of selected items. How do you count the number of selected items?

  43. Hello! Your instructions worked perfectly! My question is that I need to save to OneDrive so others can edit and update but I know that I can't do that with this file because it is saved as an xlsm file. Any way around this? Thank you!

    1. Hi! In OneDrive, you can save any files you want. But, as has been said many times in the comments to this article, the macro does not work in Excel for Web.

  44. Hi, I have 2 columns with dropdowns, Colomn 1 (a,b,c,d) and column 2 (a:123, a:456,a:789, b:123, b:456 and so forth). If I select a in column 1, I only want the data with a in column 2 to appear. How do I do this please?

      1. Thank you, I will have a look.

  45. Excellent information - thank you! Curious if I can have text displayed in the cell that disappears when a selection has been made. i.e. "Select all that apply" and then when the user clicks in the cell the dropdown list appears and if a selection is made it replaces that original text. Otherwise the cells do not have 'instructions' nor indicate there is a dropdown list. Thank you in advance!

    1. Hello! You can first write text in a cell and then create a drop-down list in that cell. Then you will see this text until you select a value from the drop-down list. You can also use Input Message as described here when creating a drop-down list: Insert a drop down list with message.

      1. Thank you so much for your timely reply! I should have mentioned I used your awesome code to allow for multiple selections, therefore the "click here to select..." message simply became part of the string of drop down responses, and did not disappear. (I also have the Input Message you referenced in use, but that only appears once the cell is clicked. I still would like the cell to contain the 'click here' message until something is selected.) I'm sure there's a way to alter the VB code to clear the cell when something is selected - is that an easy response for you? Thank you!

          1. I did follow your instructions, I'm sorry I wasn't clear on the results. Because I have your multi-select dropdown with item removal code added, the end result maintains the original message in the cell.
            For example, the result should be "Weather delay, Heavy traffic" but instead it is, "Click here to select, Weather delay, Heavy traffic".
            The code to allow multiple items from the dropdown list added them to that original "click here to select" message instead of clearing it when an item was selected from the dropdown list.

  46. Hi,

    Thanks for sharing the code!

    Is there a way to limit the number of options a user can select?

    Say - maximum 3 entries?

    I.e, once the user has selected "Chocolate, Cheese, Eggs" - have a way to prevent them from adding any more?

    1. Hi! To do this, you need to modify the macro code. We do not modify macros at the request of users, but you can do it yourself.

  47. Hi. The code didn't work for my sheet and followed your instructions. I converted it to XLSM already. Please advise.

    1. Hi! Your workbook is not available to me. Please follow all instructions carefully. You can also download the sample file linked at the end of this article.

    2. oh my list is sourced from a different worksheet so the dropdown part of your instructions didn't notate that.

  48. Thanks so much for the helpful walk-through - this is the exact fix I've been after for my work spreadsheet!

    However, after trying all 3 code strings separately in the backend of the workbook, none of them have worked :( I'm trying to figure out why this might be, as I've followed the steps above, pasted the code into the right place and the file was already a macro-enabled workbook.

    When you paste the code, do you need to delete any existing code that's already in the code window? Or just paste before/after?

    The only thing that appears different is that that a few horizontal lines appear, separating parts of the code. Could this affect the output?

    Thanks again

    1. Hello! When you insert code, pay attention that there are no macros with the same names. If you are not using any other macros, delete all the code and then insert the code for multiple selections. You can also download the sample file linked at the end of this article.

  49. Great Info. The question I have may have already been answered but there are 432 responses so I more than likely missed it. I have two issues. (1) I want to have 3 separate dropdown lists for 3 specific columns in my worksheet (List 1 for column 1; List 2 for column 2, and each list contains separate data etc.) and I want to adapt your VB code so that there is NO Duplication AND the ability to remove an entry - PLUS - I have a 4th separate dropdown list for a 4th specific column that I would like to ALLOW duplicates AND the ability to remove an entry. I think that's quite a lot in one visual basic code, but if it can be done, I'd be very happy. I would be extremely happy if (2) there was a way to sequentially number the entries in ONE COLUMN ONLY as they are entered AND for the numbering to reset if I have to remove or replace an entry. I'll be happy to show you a sample of what I need the dropdowns and code to accomplish if needed.

    Thanks

    1. Hi! In the drop-down list, you can choose to allow duplicates in individual rows, columns, or cells of your table. Duplicates are not allowed in other cells. Have a look at the above article for instructions. You must use separate code to prevent editing or removing individual cells. You can use these instructions as well: How to lock and unlock cells in Excel.

  50. Hi, sorry if this has been asked before, I didn't find it in the comments.

    Can you make a dropdown list to have a single selection or multiple selection from the same list, depending on the value of adjacent cell. For example:
    1) In column A is a simple dropdown list with two possible values: let's say "value 1" or "value 2"
    2) Depending on the selected option in cell A1, I want the dropdown list in B column to have the same dropdown list options but be able to select one or multiple options:
    - if A1 is "value 1", you can select single option in B1 dropdown list
    - if A2 is "value 2", you can select multiple options in B1 dropdown list

    Fantastic help and many thanks.

    1. Hi! Modifying the macro code is necessary for doing whatever is desired. We do not modify the code at the request of users. You can try to do it yourself.

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