How to make Excel drop down list with multiple selections

The article shows how to create an Excel drop down that allows users to select multiple items with or without duplicates.

Excel has come a long way since its inception and introduces more and more useful features with each new release. In Excel 365, they've added the ability to search within data validation lists, which is a huge time-saver when working with large sets of data. However, even with this new option, out-of-the-box Excel still only allows selecting 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. With the ability to prevent duplicates and remove incorrect items, this feature can streamline data input and improve accuracy in your Excel spreadsheets.

Excel drop down list with multiple selection

How to make Excel drop down with multiple selections

Creating a multi-select drop down list in Excel is a two-part process:

  1. First, you make a regular data validation list in one or more cells.
  2. And then, insert the VBA code at the back end of the target worksheet.

It also works in the reverse order :)

Create a normal drop-down list

To insert a drop down list in Excel, you use the Data Validation feature. The steps slightly vary depending on whether the source items are in a regular range, named range, or an Excel table.

From my experience, the best option is to create a data validation list from a table. As Excel tables are dynamic by nature, a related dropdown will expand or contract automatically as you add or remove items to/from the table.

For this example, we are going to use the table with the plain name Table1, which resides in A2:A25 in the screenshot below. To make a picklist from this table, the steps are:

  1. Select one or more cells for your dropdown (D3:D7 in our case).
  2. On the Data tab, in the Data Tools group, click Data Validation.
  3. In the Allow drop-down box, select List.
  4. In the Source box, enter the formula that indirectly refers to Table1's column named Items.

    =INDIRECT("Table1[Items]")

  5. When done, click OK.
Create a data validation list from a table.

The result will be an expandable and automatically updatable drop-down list that only allows selecting one item.

Tip. If the method described above is not suitable for you for some reason, you can create a dropdown from a regular range or named range. The detailed instructions are here: How to create Excel data validation list.

Insert VBA code to allow multiple selections

This is the core part of the process that does the magic. To turn a regular single-selection picklist into a multi-select dropdown, you need to insert one of these codes in the back end of your target worksheet:

To add VBA code to your worksheet, follow these steps:

  1. Open the Visual Basic Editor by pressing Alt + F11 or clicking the Developer tab > Visual Basic. If you don't have this tab on your Excel ribbon, see how to add Developer tab.
  2. In the Project Explorer pane at the left, double-click on the name of the worksheet that contains your drop-down list. This will open the Code window for that sheet.

    Or you can right-click the sheet's tab and choose View Code from the context menu. This will open the Code window for a given sheet straight away.

  3. In the Code window, paste the VBA code.
  4. Close the VB Editor and save your file as a Macro-Enabled Workbook (.xlsm).
Insert VBA code to select multiple items in dropdown list.

That's it! When you go back to the worksheet, your drop-down list will allow you to select multiple items: Excel drop-down list to select multiple items.

VBA code to select multiple items in dropdown list

Below is the code to make a data validation list that allows selecting multiple items, including repeated selections:

VBA code to select multiple items in Excel dropdown
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

How this code works:

  • The code enables multiple selections in all drop down lists on a particular sheet. You do not need to specify the target cell or range reference in the code.
  • The code is worksheet specific, so be sure to add it to each sheet where you want to allow multiple selections in drop down lists.
  • This code allows repetition, i.e. selecting the same item several times.
  • The selected items are separated with a comma and a space. To change the delimiter, replace ", " with the character you want in DelimiterType = ", " (line 7 in the code above).

Excel multi-select dropdown without duplicates

When selecting from a large list, users can sometimes pick the same item more than once without noticing. The code below solves the problem of duplicates in a multi-selection data validation drop down list. It lets users pick a particular item only once. If you try to select the same item again, nothing will happen. Pretty cool, right?

VBA code to create multi-select drop down with no repeats
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 = ", "   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 If newValue <> "" Then If oldValue = newValue Or _ InStr(1, oldValue, DelimiterType & newValue) Or _ InStr(1, oldValue, newValue & Replace(DelimiterType, " ", "")) Then Destination.Value = oldValue Else Destination.Value = oldValue & DelimiterType & newValue End If End If End If End If   exitError: Application.EnableEvents = True End Sub   Private Sub Worksheet_SelectionChange(ByVal Target As Range)   End Sub

Multi-selection dropdown with item removal

When users need to select multiple options but can make mistakes or change their minds during the selection process, a multi selection dropdown that allows for the removal of incorrect items can be a lifesaver.

Consider a scenario where you need to assign multiple tasks to team members using a drop-down list. With Excel's default functionality, the only way to remove an incorrectly assigned task is by clearing the entire contents of the cell and starting over. With the ability to remove individual items from the selection, the team can effortlessly modify task assignments without confusion or errors.

The code below implements the item removal functionality in a simple and intuitive way: the first click on an item adds it to the selection, and a second click on the same item removes it from the selection.

VBA code to create multi-select drop down that allows removing selected items
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 = ", " 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   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)   End Sub

The below demo highlights how the multi selection dropdown with removal functionality works in Excel. The users can select multiple options from the data validation list and make adjustments on the fly. A streamlined and effective approach to managing selections!
Select and remove multiple items in Excel dropdown.

How to make a multiple selection dropdown with custom delimiter

The character that separates items in the selection is set in the DelimiterType parameter. In all the codes, the default value of this parameter is ", " (a comma and a space) and it is located in line 7. To use a different separator, you can replace ", " with the desired character. For instance:

  • To separate the selected items with a space, use DelimiterType = " ".
  • To separate with a semicolon, use DelimiterType = "; " or DelimiterType = ";" (with or without a space, respectively).
  • To separate with a vertical bar, use DelimiterType = " | ".

For example, if you change the delimiter to a vertical slash, your multi-select picklist will look as follows: A multi-selection dropdown with a custom delimiter

How to create dropdown with multiple selections in separate lines

To get each selection in a separate line in the same cell, set DelimiterType to Vbcrlf. In VBA, it is a constant for the carriage return and line feed characters.

More precisely, you change this code line:

DelimiterType = ","

to this one:

DelimiterType = vbCrLf

As a result, each item that you select from the dropdown list will appear in a new line: A dropdown list with multiple selections in separate lines

How to create multi-select dropdown for specific columns, rows, cells and ranges

All the codes described in this tutorial work across an entire sheet. However, you can easily modify any of the codes, so it only applies to specific cells, rows, or columns as needed. For this, find this line of code:

If rngDropdown Is Nothing Then GoTo exitError

Add immediately after it, add a new line specifying where to allow multiple selections, as explained in the below examples.

Multi-select drop-down for specific columns

To allow selecting multiple items in a certain column, add this code:

If Not Destination.Column = 4 Then GoTo exitError

Where "4" is the number of the target column. In this case, the multi-select dropdown will be only enabled in column D. In all other columns, dropdown lists will be limited to a single selection.

To target several columns, specify their numbers using this code:

If Destination.Column <> 4 And Destination.Column <> 6 Then GoTo exitError

In this case, the multi-select dropdown will be available in columns D (4) and F (6).

Multi-selection dropdown for certain rows

To insert multiple drop-downs in a specific row, use this code:

If Not Destination.Row = 3 Then GoTo exitError

In this example, replace "3" with the row number where you want to enable multi-select dropdowns.

To target multiple rows, the code is as follows:

If Destination.Row <> 3 And Destination.Row <> 5 Then GoTo exitError

Where "3" and "5" are the rows where selecting multiple items is allowed.

Multiple selections in specific cells

To enable multiple selections in particular cells, add one of the below code lines.

For a single cell:

If Not Destination.Address = "$D$3" Then GoTo exitError

For multiple cells:

If Destination.Address <> "$D$3" And Destination.Address <> "$F$6" Then GoTo exitError

Just remember to replace "$D$3" and "$F$6" with the addresses of your target cells.

Multi-select drop-down for specific range

To limit your multi-select dropdown to a particular range, replace this line of code:

If Intersect(Destination, rngDropdown) Is Nothing Then

with this one:

If Intersect(ActiveCell, Range("С3:D10")) Is Nothing Or Intersect(Destination, rngDropdown) Is Nothing Then

The range of interest is specified directly in the code (С3:D10 in the above example). This modification offers a more efficient approach to handing ranges - instead of individually listing 16 cells, you use a single range reference.

How to enable multi-selection functionality in protected sheet

To enable a multi-select dropdown functionality in a protected worksheet, simply insert the following code into the sheet where you've added the primary code.

VBA code to allow multiple selections in protected sheet
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

Before adding this code to your worksheet, remember to replace "password" with the actual password you used to protect the sheet. And this is the only change that needs to be made. The code will automatically detect the presence of a dropdown list in a given cell and allow editing of that cell. In all other cells, editing will remain restricted.

Note. Please be aware that including your real password in the code could lead to a security risk. To ensure the safety of your workbook, store it in a secure location that is protected against unauthorized access or use.

So, there you have it - an Excel dropdown list with multiple selections. Adding this awesome feature to your spreadsheets will increase the accuracy of your data input and help you get your work done faster!

Practice workbook for download

Multi-selection dropdown - examples (.xlsm file)

428 comments

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

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

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

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

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

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

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

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

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

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

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

    • 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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

      • Thank you! This solved the issue

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

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

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

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

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

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

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

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

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

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

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

      • 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

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

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

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

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