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 5. Total comments: 658

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

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

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

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

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

  6. Hi,

    Great article!

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

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

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

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

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

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

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

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

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

  15. Can't thank you enough.

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

  17. Thanks a lot for this article!

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

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

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

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

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

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

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

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

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

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

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

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

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

  31. Hi,
    Thanks for this post, was really helpful and clear.
    I used it for a file I created and used a drop down list with multiple selection, and I did the change in the code so it will be presented one below the other and not separated with ","

    But, for some reason, sometimes some of the cells with the data gets tempered and it jumps and being separated by space.
    example:
    1. when it's correct:
    Kitchen
    Bathroom
    Room

    2. When it's changed (I don't know what causes this or how to even try to fix it):
    Kitchen Bathroom Room

    I tried using alt+Enter, it shows an error: "this value doesn't match the data validation restrictions defined for this cell."
    another thing is that this is a multiple selection WITH option to remove. but when it gets tempered - selecting for removal doesn't work - it adds it again.

    Hope I explained this clearly and that someone can help me with it.

    Thanks,
    Sheena

    1. My mistake, when it's tempered, it just connects all the data: KitchenBathroomRoom like this.

      1. Hi! If my understanding is correct, you can see the working code on the "Selections in Separate Rows" worksheet in the sample file that is linked to at the end of this article. Hope you’ll find this information helpful.

        1. I downloaded the worksheet, and it doesn't allow multiple choice when I try to do it myself.
          In my excel file, it does allow me to select multiple lines from the drop down and it indeed presents it to me one under the other.

          But not sure why it sometimes gets tempered and gets out of order.

            1. Hi again, to clarify, I have no spaces in any of my drop down items, they are all single word items without a comma or space. I've made a brand new workbook to test it with items "test", "test2", "test3", and "test4"

              There are no delimiters in any of the drop-down items. When I have 3 options selected and remove 1 of them, by manually pressing backspace on the text, it comes with a validation error. When I turn off the error notification, it brings back the removed item and duplicates the text that was remaining, so "test, test2, test3" becomes "test, test2, test3, test, test2" when I try to remove "test3" then becomes "testtest2test3testtest2" when I select the text and press enter

              I am using the "Multi-selection dropdown with item removal" code

              1. If you want to manually adjust the data in a cell, why do you need a dropdown list? Try doing it in a normal dropdown without multiple selection and you will get an error too. If you use the "Multi-selection dropdown with item removal" code, you can't get a text string with duplicates of "test, test2, test3, test, test, test2".

            2. Hi, I also have their problem, and my macro is not blocked. The code works except from when I manually edit or enter the text without using the drop down menu by either using F2 and pressing enter or by clicking the formula bar and pressing enter. As long as there is more than one option selected when I press enter it will either think I've made a data validation error or if will remove all delimiters and continue to generate duplicates of the text everytime I try to remove the error unless I delete all the text in the cell.

              so "kitchen, bathroom, Room" will turn into "kitchenbathroomRoom" then when I try to remove the "Room" it then becomes "kitchenbathroomRoom, kitchenbathroom" or if I try to remove "Room" from "kitchen, bathroom, Room" it becomes "kitchen, bathroom, Room, kitchen, bathroom"

              1. Hi! Your problem has been discussed many times in the comments. The solution to this issue is also described in the article at the end of the second section. Please read it carefully. The same character cannot be a delimiter and part of the dropdown items. In our code, the default delimiter is a comma followed by a space character (", "), so this combination of characters should not be used anywhere in the dropdown list items themselves to avoid conflicts.

  32. Wow, this does exactly what I wanted it to!
    One question regarding putting a filter over columns with multiple items ticked. Say I have 2 different rows:
    eggs
    eggs, milk

    When I filter this data I wish to just press on "eggs" and have them both be live, rather than displaying as two different options in the filter.

    Is this possible? It is my first time using VBA code!

    1. Hi so I created a multi cell drop down list but I need to do more.

      1. In a different cell I need to show the items which was not selected in a my multi drop down list cell.
      2. I need to sum up my chosen options from my multi drop down list and my items not elected

      1. Hi! An Excel drop-down list with multiple selections creates a string of text in a cell that contains all the items that were selected. To do what you want, each item must be written in a separate cell.

  33. Appreciate you posting this. Very helpful information and you helped me do exactly what I was looking to do. Essentially I was looking for Excel to mimic a function that MS Project does, which is assign resources to specific task items. I created a list of "Leads" or techs. Then applying you code. I utilized the code that included removal. I also added the column destination line as well as changing the comma delimiter with the drop line vbCtLf. It all works great. The one issue I have is, that when more than one name is selected, i get a data verification error. Doesn't matter if I am using the original comma delimiter or drop line. Data verification sees multiple names and marks it.

    It's a minor issue and if the answer is no, it can't be fixed, I am fine with that. But because I'd prefer the cleanest code possible, I would like to ask. Is there a way I can correct the process above to allow me to select multiple names and not trigger a data verification error. That is without turning off data verification off completely. Even if I just got it to ignore errors for this one column of one workbook, that could work.

    Aside from this minor issue, your code is great. Thank you for posting and sharing.

    1. Hi! I don't know what changes you made to the code or what data you used for the dropdown list. Try downloading the sample file from the link at the end of this article and pay attention to the worksheet "Selections in separate lines". The code works without errors there.
      Also make sure that the delimiter is not used in the list of values for the dropdown list.

  34. When I hit "Enter" to move to the next cell, it is auto posting the drop-down items selected. Any script on stopping auto post-back?

    1. Hi! I am not sure I fully understand what you mean. The macro doesn't change how the dropdown works, it just lets you select multiple values.

      1. I am allowing customization within my dropdown list selection. So not allowing an error alert. The macro allows for me to select multiple items and allows for the customization until I move to the next cell. Then it duplicates the drop-down items selected.

        1. Hi! I don't really understand what kind of customization you are talking about. I can't understand and reproduce your problem. I recommend downloading the sample file from the link at the end of the article and see how the macro works.

        2. It's almost like I need to allow for the error and stop the duplication of the new value. Does that make sense?

  35. Hi, the code appears to be working correctly and allows me to choose multiple items from my list; however, I am getting a note in the cell that says "data validation error" with a yellow diamond/exclamation and says "The value in this cell is invalid or missing. Click on 'Display Type Information' for specific details". The popup then says "Field Type Information, G2, Data Type: List, Restriction: Value must match one of the listed items."

    Again, it appears to be working correctly, just want to get rid of this warning box!

    Thank you!

    1. Hi! Without seeing your data, it is impossible to give any recommendations. Check what data you are using for the drop-down list. It may be very long numbers or text strings with non-printable characters, dots, or commas. Such values may change when you copy and paste.

      1. Hi, thanks for the step-by-step instructions, very helpful. I am experiencing the same issue as Emma.

        The context is that I'm using multiple selection dropdown with a custom delimiter for a column that validates from a list, formatted as a table. However, after selecting multiple items from the list, the cell was showing Data validation error icon - a triangle with an exclamation mark, saying the restriction that 'Value must match one of the listed items.'

        This may be because the multiple items with custom delimiter that were displayed upon selection do not match/part of the referred list/Table.

        Again, everything is working, but the same question, how do we stop that data validation error icon from showing up?

        1. Hi! I can't see your data, so in addition to the previous recommendations, I can only assume you're using a list separator character in your values.

          1. Hi, thanks for responding. Yes that is correct, I am using " | " as a custom delimiter to separate selected items in one cell. So using the same example as yours under section 'How to make a multiple selection dropdown with custom delimiter', I would have Chocolate | Cinnamon on one cell, but there's Data validation error icon showing up on the left side of the cell.

            1. Also, is there a possibility to set the selected items sorted by alphabetical order? For example, from the options eggs, cheese, breadcrumbs, so upon choosing the times, even though we select the items as such order, the cell will populate as breadcrumbs, cheese, eggs?

              It would also be great if there's a way to show the dropdown as checkbox, so we can make multiple selections in one dropdown.

              Thanks Alexander.

              1. Hi! To sort the words in the text string that the drop-down list creates, you need a special VBA macro. Showing the drop-down list as a checkbox cannot be defined with a macro. We cannot modify Excel.

  36. Can this be done with Excel Online which does not allow you access VBA?

    2. I am trying to figure it out the same thing :( For me it works in the Excel App but when we open the spreadsheet in the Browser, it does not work.

  37. Hi!

    I have a multi-select dropdown list with the ability to remove using your VBA code. Is there additional code available to automatically sort the selected list items alphabetically when they are selected instead of placing them in the order they were selected. For instance if I select items 1, 3, 5 but in the order of 5, 3, 1, they are listed as 5, 3, 1. I would like them to list as 1, 3, 5 despite the order I select them in.

    Thanks!

    1. Hi! This question has been asked before, and the answer was "no". The drop-down list creates a text string. You can try to sort it using an additional VBA macro.

  38. Thank you, this code is really helpful. Our organisation, a charity, may want to make available at no cost an excel file that includes a modified version of this code. Can we do so? And, if so, how would you like to be credited?

  39. I have a worksheet that requires 4 different multi drop down lists for 4 different columns. I have tried to replicate what i did for the first one but it does not like me changing the formula ie =INDIRECT("Table1[Items]") to =INDIRECT("Table2[Service]"). Can you only have one multi drop list?

  40. Hi, thank you for sharing the codes. In situations when you have a large list, is there a way that we can type the text and it automatically select the selection in the list? instead of having to go through the whole list to find that one selection.

    2. And how can I use pivot table to organize all the entries without duplications.

      1. Hi! The pivot table does not use the standard dropdown list. The code works only with the dropdown list.

  41. Hi, I tried making the Multi-selection dropdown with item removal and it works only for some items on my list. It looks like it doesn't work for items with '&' in it. Is there a way to fix that?

    1. Hi! Unfortunately, I have not been able to reproduce your problem. The '&' symbol has no effect on the workings of the code.

  42. Hi! I need to do the multiple drop down in multiple columns. I have tried adding another range line
    If Intersect(ActiveCell, Range("D4:D79")) Is Nothing Or Intersect(Destination, rngDropdown) Is Nothing Then
    'do nothing
    or adding another section of code (copying the code again but changing the range it affects) but this does not work. Is there a way to do more than one in an excel sheet?

    1. Hi! Use the Union function to check whether the active cell is in one of the ranges.
      For example:

      If Intersect(ActiveCell, Union(Range("C3:D10"), Range("C15:D17"))) Is Nothing Or Intersect(Destination, rngDropdown) Is Nothing Then

      I hope it’ll be helpful.

      1. Thank you! This solved the issue

  43. Hello Alexander, I hope you are well. Great tutorial, thank you for sharing.

    Can you show a modified version of the code to only allow multiple selection for a selection of dropdowns over a certain cell range please?

    Grateful for your help with this - thank you.

    Kind regards,

    Richard.

    1. Hi! Please read the following paragraph in the article above very carefully: Multi-select dropdown for specific columns, rows, cells and ranges.

      1. Thank you for your quick reply Alexander, I appreciate that. Clearly I need to get my eyes tested lol

  44. Is there a way to add the code over every sheet in a workbook without having to do each sheet individually, so as you add sheets, they already have the code?

  45. Codes are working fine but for protected sheet I am able to make a single entry only. Multiple entry is not possible with it when sheets are protected. I have applied your code for password protected sheet but it is not working.

  46. The multiselect with removal does not remove the first entry. It will remove all other entries but does not return to blank.

  47. Hello, Is there a way to ensure your drop down list is always in alphabetical order and not click order?

    1. I sort the source to be A-Z, then drop down list appear in A-Z order as well.

    2. Hi! A drop-down list creates a text string in a cell. You need a special macro to sort the words in this text string.

  48. How do I make a field on the drop down where they can enter text as and "other" option?

  49. I'm trying to use your code to enable multi-selection functionality in protected sheet, but I don't know where to put this code in the VBA (I'm also using your code for "Multi-selection dropdown with item removal") Can you please provide me with the exact location where this code should be placed?

    1. Hi! This has been discussed many times in the comments of this article. Add the code after the "Multiple selection drop down with item removal" code or whatever code you use.

      1. Thanks so much for your quick response! I went through the old comment around this topic but doesn't seem like they received any solutions. I did try put the code for "Enable multi-selection functionality in protected sheet" after my main code "Multi-selection dropdown with item removal". it's still doesn't work. It's giving me single select of the latest one I selected. Please see below my code and any help will be greatly appreciated.

        Option Explicit
        Private Sub Worksheet_Change(ByVal Destination As Range)

        Dim rngDropdown As Range
        Dim oldValue As String
        Dim newValue As String
        Dim DelimiterType As String
        DelimiterType = vbCrLf
        Dim DelimiterCount As Integer
        Dim TargetType As Integer
        Dim i As Integer
        Dim arr() As String

        If Destination.Count > 1 Then Exit Sub
        On Error Resume Next

        Set rngDropdown = Cells.SpecialCells(xlCellTypeAllValidation)
        On Error GoTo exitError

        If rngDropdown Is Nothing Then GoTo exitError
        If Destination.Column 8 And Destination.Column 10 And Destination.Column 12 And Destination.Column 14 And Destination.Column 16 And Destination.Column 18 And Destination.Column 20 And Destination.Column 22 And Destination.Column 24 And Destination.Column 26 Then GoTo exitError

        TargetType = 0
        TargetType = Destination.Validation.Type
        If TargetType = 3 Then ' is validation type is "list"
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        newValue = Destination.Value
        Application.Undo
        oldValue = Destination.Value
        Destination.Value = newValue
        If oldValue "" Then
        If newValue "" Then
        If oldValue = newValue Or oldValue = newValue & Replace(DelimiterType, " ", "") Or oldValue = newValue & DelimiterType Then ' leave the value if there is only one in the list
        oldValue = Replace(oldValue, DelimiterType, "")
        oldValue = Replace(oldValue, Replace(DelimiterType, " ", ""), "")
        Destination.Value = oldValue
        ElseIf InStr(1, oldValue, DelimiterType & newValue) Or InStr(1, oldValue, newValue & DelimiterType) Or InStr(1, oldValue, DelimiterType & newValue & DelimiterType) Then
        arr = Split(oldValue, DelimiterType)
        If Not IsError(Application.Match(newValue, arr, 0)) = 0 Then
        Destination.Value = oldValue & DelimiterType & newValue
        Else:
        Destination.Value = ""
        For i = 0 To UBound(arr)
        If arr(i) newValue Then
        Destination.Value = Destination.Value & arr(i) & DelimiterType
        End If
        Next i
        Destination.Value = Left(Destination.Value, Len(Destination.Value) - Len(DelimiterType))
        End If
        ElseIf InStr(1, oldValue, newValue & Replace(DelimiterType, " ", "")) Then
        oldValue = Replace(oldValue, newValue, "")
        Destination.Value = oldValue
        Else
        Destination.Value = oldValue & DelimiterType & newValue
        End If
        Destination.Value = Replace(Destination.Value, Replace(DelimiterType, " ", "") & Replace(DelimiterType, " ", ""), Replace(DelimiterType, " ", "")) ' remove extra commas and spaces
        Destination.Value = Replace(Destination.Value, DelimiterType & Replace(DelimiterType, " ", ""), Replace(DelimiterType, " ", ""))
        If Destination.Value "" Then
        If Right(Destination.Value, 2) = DelimiterType Then ' remove delimiter at the end
        Destination.Value = Left(Destination.Value, Len(Destination.Value) - 2)
        End If
        End If
        If InStr(1, Destination.Value, DelimiterType) = 1 Then ' remove delimiter as first characters
        Destination.Value = Replace(Destination.Value, DelimiterType, "", 1, 1)
        End If
        If InStr(1, Destination.Value, Replace(DelimiterType, " ", "")) = 1 Then
        Destination.Value = Replace(Destination.Value, Replace(DelimiterType, " ", ""), "", 1, 1)
        End If
        DelimiterCount = 0
        For i = 1 To Len(Destination.Value)
        If InStr(i, Destination.Value, Replace(DelimiterType, " ", "")) Then
        DelimiterCount = DelimiterCount + 1
        End If
        Next i
        If DelimiterCount = 1 Then ' remove delimiter if last character
        Destination.Value = Replace(Destination.Value, DelimiterType, "")
        Destination.Value = Replace(Destination.Value, Replace(DelimiterType, " ", ""), "")
        End If
        End If
        End If
        Application.EnableEvents = True
        Application.ScreenUpdating = True
        End If

        exitError:
        Application.EnableEvents = True
        End Sub

        Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        ActiveSheet.Unprotect Password:="DVonly"
        On Error GoTo exitError2

        If Target.Validation.Type = 3 Then
        Else
        ActiveSheet.Protect Password:="DVonly"
        End If

        Done:
        Exit Sub

        exitError2:
        ActiveSheet.Protect Password:="DVonly"
        End Sub

        1. Hi! There are no errors in the code. But I can't look in your workbook and your Excel to find the cause of the problem. Try installing the code in a new workbook or downloading the sample file from the link at the end of the article.

          1. I created a brand new file and copied the Multi-selection dropdown with item removal code and added If Not Destination.Column = 4 Then GoTo exitError before the "TargetType = 0" line. This worked as expected. I added the How to enable multi-selection functionality in protected sheet code at the end. The new file has "password" as the password. When I click on the drop down, the file changes to the sheet with the Table for the drop down and changes the Table to a list, not adding the second selection to the drop down.

            1. Hi! Unfortunately, I did not have a replication of your problem in my workbook. Please note that the code to enable the multi-select functionality in the protected sheet should be added at the end of your code, after the main code.

  50. Is there any way to apply multiple dropdown feature on web excel ? Like on "OneDrive"

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