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

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

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

  3. This isexactly what I was looking for
    And was super easy to do
    thank you for that
    I love you

  4. How would I get the vba to look at column header instead of number? I have a column called "Area of Impact" and if I add or move columns, the VBA wont stay on that column.
    Thanks for the code by the way, exactly what I was trying to do.

    1. Hi! I'm really sorry, we cannot help you with this. We do not do VBA code creation or customization on request.

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

  6. This worked (it took me a minute because I put the code on the sheet that had the list values first instead of the sheet that I was putting the selections on - I left the code on both sheets and there's no problem, so that's nice too!).

    I am super excited about the multi-select - with removal! - because I can have one column instead of 2 (one to say multiple items and a second to list out multiple items). This will make for a smaller, less intimidating spreadsheet for the people who have to fill it out - and it cuts down on entry errors. Win win all around!

    Thank you!!

  7. This is really clever and useful work! Thank you so much for sharing. I've used the code for multiple selection with item removal, and it works just fine. However, as soon as I select more than one item to appear in the cell, there's a cell data warning triangle sign, telling me this: "The value in the cell is invalid or missing. Click on 'Display Type Information' for specific details.", and then, this Field Type Information says this: "Data Type: List. Restriction: Value must match one of the listed items." Do you happen to know what this is about?

    1. Hello Constantine!
      Unfortunately, I was not able to reproduce your problem. VBA code to select multiple items in dropdown list works for me without errors. Note that the same character cannot be used as a delimiter and part of a drop-down list at the same time. That warning is in the article above.
      If this does not help, explain the problem in more detail. Give an example of the data you use in the drop-down list, what list separator you use.

      1. Thank you for your response, Alexander! On my end I'll play a bit more with the scripts and data in a new spreadsheet, sometime next week, to see if I can get a better feel for the whole idea. I think I need to redo everything from scratch, to avoid any errors. I'll get back to you if/when I have clearer questions.

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

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

  10. Hello,

    I am by no means tech savvy, but your instructions were clear, and I was able to create a spreadsheet with multiple drop downs and I am very much appreciative of you. I would like to be able to change text in a cell after selecting a drop down to customize it. For example, my drop downs include:

    Background, pg.
    Policy, pg.
    Policy, Section, pg.

    I would like to be able to change text within a cell and not necessarily within the table for example

    A4. Background, pg. 1
    A5. Policy, pg. 6
    A6 Policy, Section 1a, pg. 6

    Currently, when I change the text, or strikethrough something (to keep history) i.e., Background, pg. to Background, pg. 1 they will both appear in the drop down. When I try to delete one, Background, pg. 1 will appear 2x along with Background, pg. Also, if I try to strikethrough an item on the list it will duplicate the whole list.

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

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

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

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

  15. I have two columns that have are using a drop down list and the code is working on one of the columns but not on the other. Is there something I am missing? My understanding is the code should work on any lists on the sheet?

    1. Hi! I don't know what code you have installed. The article above suggests codes that work on entire worksheet, but can also work in individual columns, rows and cells: Multi-select dropdown for specific columns, rows, cells and ranges.

  16. I can't get the code to work (multi select with delete) when I have the worksheet protected. I added the password sub code you provided to the Sheet2 code (pasted between Option Explicit and Private Sub Worksheet_Change(ByVal Destination As Range)). Is there anything I need to add to UserInterfaceOnly to get this to work?

    1. Hi! It is enough to follow all the instructions given in the article above. Or use the sample file from the link at the end of the article.

      1. Hmm. I have tried it several times and can't get the code to work, even on the sample file. I can only ever select one item from the drop down when the form is protected. Are there any other trouble shooting areas I can look to?

        1. Hi! Unfortunately, I can't check the code in your workbook. All necessary instructions are in the article above.

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

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

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

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

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

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

  23. Awesome, many thanks for teaching a very old dog new tricks!

  24. THANK YOU! This has massively helped me in what I was trying to achieve. Really clear instructions that actually work. I'm not going to pretend to understand it all but I don't need to!
    Thanks again.

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

  26. Hi,

    Great article!

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

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

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

  29. Hi,

    I've been playing around with these macro's and everything works great! Thanks a lot for the clear instructions.

    Unfortunately, sometimes suddenly the scroll bar in the selection menu seems to stop working, and then randomly starts working again. I was wondering if anyone else is having this issue and whether there is a known cause (and maybe even a fix, but I'll settle for a cause :-D).

    I should note that I'm on the semi-annual enterprise edition of Excel, so I unfortunately don't have the most recent features that are released (including searching through the dropdown menu, which hopefully will come soon).

  30. Hi! I am trying to create a worksheet that will populate a weekly meeting activity when you select a "week of" date from a drop-down box. Is there a way to do this?

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

  32. This was incredibly clear and helpful, great work here, thank you!

  33. Thank you very much for giving the codes and explain how to modify them to make them more flexible.

  34. Thanks for the code,

    The problem i am facing is that when remove the words from the cell i cant remove them all, there will always be one word that can't be removed.

    please help

    1. Hi! I can't see your data. Check what characters you are using in your values. Follow the instructions carefully.

  35. Can't thank you enough.

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

  37. Thanks a lot for this article!

  38. Hi. the multiple dropdown worked. but is affected others columns. how to adjust?

    1. Hi! Pay attention to the following paragraph of the article above: Multi-select dropdown for specific columns, rows, cells and ranges.

      1. thanks sir. sorry.

        btw, how about if i want to make if arguments across two columns?

        i have two different data in two columns.

        columns 1 columns 2
        data 1 data 1
        data 2 data 1
        data 3 data 1

        if user enter data 1 from either from columns 1 or columns 2, it will resulted to 0.

        are there any formula using if arguments?

        1. Hi! Unfortunately, I don't really understand what you want to do and how it relates to the topic of this article. The drop-down list selects a value from the list and places it in the cell. The IF function is not used. Explain your question.

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

  40. Could you provide a sample workbook with the password protected multi-selection functionality enabled. Scanning through the comments, this seems like the only tricky part of the awesome code and solutions that you've provided.

    1. Hello! Just follow all the instructions and add the code provided in the article above. Make sure that you do not have two Worksheet_SelectionChange functions with the same name in your code.

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

  42. It worked!!! I am so happy!!! You & your team’s patience and dedication at work is an invaluable gift for all of us and we are truly grateful.
    Wishing you and your loved ones continual good health and safety. Kind regards from Malaysia :)

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

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

  45. I'm not able to attach a picture of my issue, however, I have copied the code into the Worksheet/SelectionChange page. I continue to be prompted to name a Macro. Then I get a Compile Error: Ambiguous name detected: Worksheet_SelectionChange. Any ideas? I have Windows 11.

    1. Hi! I cannot know how you installed the code. Follow the instructions in the article above carefully. In the workbook linked at the end of the article, you can see an example of how the code works.

  46. Hi
    Thanks really finding your site useful.
    Have been quite successful with your guidance, so to take it further
    , how can I get my drop down boxes to reset when I want to put a new entry in from the start, ie enter a name drop down gives age and favorite colour. then enter a new name and boxes return to new entries not the previous ones.

    Hope this makes sense
    Thank you

    1. Hi! If I understand correctly, you can clear a cell with a drop-down list just like any other cell - by using the DEL key.

  47. Please ignore my last question! just seen the answer in the main body - I happy went and implemented before reading the WHOLE post. Apologies

  48. Is there any way I can amend the code to target only one specific dropdown box?

    I have a number of them on the same sheet.

    Thanks so much for this - its an invaluable resource.

    1. Hi! To allow multiple selections for only one specific dropdown box, use these instructions from the article above: Multiple selections in specific cells.

  49. Hi! Really appreciate the article and code. Thanks!

    The task is to create a product list with fixed prices, for which you can add optional accessories at additional cost. One column for 'Product' , one for 'Price' and one for 'Accessories'.
    What I want to do, is to make the accessories column a drop-down list (sourced from a master accessories and prices table) where I can add whichever accessories I wish, and the total price automatically updates depending on the attachments selected. Should also support accessory/item removal in case of mistakes.

    Thanks in advance!

  50. Hi there, is it possible to have the final result of the multiple selection arranged in alphabetical order?

    1. Hi! See the comments below. The answer has already been given. You will need to use a separate macro to sort the text string.

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