How to Make Multiple Selections in a Drop Down List in Excel

Written by Last updated October 22, 2026

📋 FREE EXCEL TIPS EBOOK - Click here to get your copy

You can use VBA to select multiple items from an Excel drop-down list and keep them together in the same cell.

One of my colleagues asked me if it is possible to make multiple selections in a drop-down list in Excel.

When you create a drop-down list, you can only make one selection. If you select another item, the first one is replaced with the new selection.

He wanted to make multiple selections from the same drop down in such a way that the selections get added to the already present value in the cell.

Something as shown below in the pic:

New York and York selected together in cell C2, with the city drop-down open.

In this tutorial, I will show you how to make multiple selections in an Excel drop-down list (with repetition and without repetition).

This has been one of the most popular Excel tutorials on this site.

Follow along with the example file

Multiple Selections in Excel Drop Down List.xlsm

This Article Covers

Watch Video – How to Select Multiple Items from an Excel Drop Down List

The video shows the original approach. Use the code below for the examples here, including the corrected check that treats New York and York as different cities.

Create the Drop-Down List

There are two parts to creating a drop-down list that allows multiple selections: creating the list and adding the VBA code to the worksheet.

Excel’s standard Data Validation list doesn’t have a setting that keeps successive choices in one cell. The VBA below adds that behavior; a formula or Office Script isn’t a direct replacement for this selection-change event.

These examples were tested in desktop Excel on Windows. Excel for the web cannot run VBA, and this workbook has not been tested on Mac.

Below I have a list of cities, and I want to select several of them in cell C2.

Five source cities in column A and an empty Selected Cities cell C2.

The example file already has the lists and code. If you’re building this in your own workbook, enter the five cities in A2:A6, then follow these steps:

Select cell C2, or select the range of cells where you want the drop-down lists.

Cell C2 selected before creating its drop-down list.

Click the Data tab.

The Data tab on the Excel ribbon.

Click Data Validation in the Data Tools group.

The Data Validation dialog before choosing a validation type.

On the Settings tab, choose List from the Allow menu.

List selected in the Allow field of Data Validation.

In the Source field, enter =$A$2:$A$6.

The Data Validation Source field set to =$A$2:$A$6.

Click OK.

The completed city drop-down list in C2.

Now, cell C2 has a drop-down list showing the cities in A2:A6. At this point, choosing another city still replaces the previous one.

Select Multiple Items Without Duplicates

A lot of people have been asking about the code to select multiple items from a drop-down list without repetition.

Below is the city list from the No Duplicates sheet, where the selections will go into C2.

The No Duplicates example before selecting cities.

Here is the VBA code. Copy the entire block, including the helper function at the end:

Option Explicit

'Paste into the worksheet's code module, not a standard module.
'Use the drop-down or type one valid choice. Bulk edits replace values.
'Ordinary paste can replace validation. Use Paste Values for a single choice.
'Other VBA that writes to these inputs must disable events around its writes.
Private Sub Worksheet_Change(ByVal Target As Range)
    Const InputCells As String = "C2"
    Const Separator As String = ", "
    Const AllowRepeats As Boolean = False
    Dim NewValue As String, OldValue As String, Result As String
    Dim Entry As Variant, AlreadySelected As Boolean
    Dim PreviousEvents As Boolean, EventsChanged As Boolean
    Dim Undone As Boolean, Failure As String
    Dim ValidChoice As Boolean

    If Target.CountLarge <> 1 Then Exit Sub
    If Intersect(Target, Me.Range(InputCells)) Is Nothing Then Exit Sub
    If Not HasListValidation(Target) Then Exit Sub
    On Error GoTo Failed
    If Not IsError(Target.Value2) Then NewValue = CStr(Target.Value2)
    If Len(NewValue) = 0 And Not Target.HasFormula And Not IsError(Target.Value2) Then Exit Sub
    ValidChoice = Target.Validation.Value And Not Target.HasFormula And Not IsError(Target.Value2)

    PreviousEvents = Application.EnableEvents
    Application.EnableEvents = False
    EventsChanged = True
    Application.Undo
    Undone = True
    If IsError(Target.Value2) Or Target.HasFormula Then
        OldValue = ""
    Else
        OldValue = CStr(Target.Value2)
    End If

    If Not ValidChoice Then
        Failure = "Choose one city from the drop-down list. The previous selections have been kept."
        GoTo Finished
    End If

    'The separator cannot also appear inside an individual list item.
    If InStr(1, NewValue, Separator, vbBinaryCompare) > 0 Then
        Failure = "This item contains the selection separator. Choose a separator that is absent from the list items."
        GoTo Finished
    End If

    If Len(OldValue) = 0 Then
        Result = NewValue
    Else
        If Not AllowRepeats Then
            For Each Entry In Split(OldValue, Separator)
                If StrComp(CStr(Entry), NewValue, vbTextCompare) = 0 Then
                    AlreadySelected = True
                    Exit For
                End If
            Next Entry
        End If
        If AlreadySelected Then
            Result = OldValue
        Else
            Result = OldValue & Separator & NewValue
        End If
    End If
    If Len(Result) > 32767 Then
        Failure = "The selections would exceed Excel's cell text limit. Clear this cell and start a shorter list."
        GoTo Finished
    End If
    Target.Value2 = Result

Finished:
    If EventsChanged Then Application.EnableEvents = PreviousEvents
    If Len(Failure) > 0 Then MsgBox Failure, vbExclamation, "Multiple selections"
    Exit Sub

Failed:
    Failure = "The selections could not be combined: " & Err.Description
    'Undo may be unavailable for a programmatic edit. Do not undo again.
    On Error Resume Next
    If Undone Then Target.Value2 = OldValue
    If EventsChanged Then Application.EnableEvents = PreviousEvents
    On Error GoTo 0
    MsgBox Failure, vbExclamation, "Multiple selections"
End Sub

Private Function HasListValidation(ByVal Cell As Range) As Boolean
    On Error Resume Next
    HasListValidation = (Cell.Validation.Type = xlValidateList)
    On Error GoTo 0
End Function

Option Explicit'Paste into the worksheet's code module, not a standard module.'Use the drop-down or type one valid choice. Bulk edits replace values.'Ordinary paste can replace validation. Use Paste Values for a single choice.'Other VBA that writes to these inputs must disable events around its writes.Private Sub Worksheet_Change(ByVal Target As Range)    Const InputCells As String = "C2"    Const Separator As String = ", "    Const AllowRepeats As Boolean = False    Dim NewValue As String, OldValue As String, Result As String    Dim Entry As Variant, AlreadySelected As Boolean    Dim PreviousEvents As Boolean, EventsChanged As Boolean    Dim Undone As Boolean, Failure As String    Dim ValidChoice As Boolean    If Target.CountLarge <> 1 Then Exit Sub    If Intersect(Target, Me.Range(InputCells)) Is Nothing Then Exit Sub    If Not HasListValidation(Target) Then Exit Sub    On Error GoTo Failed    If Not IsError(Target.Value2) Then NewValue = CStr(Target.Value2)    If Len(NewValue) = 0 And Not Target.HasFormula And Not IsError(Target.Value2) Then Exit Sub    ValidChoice = Target.Validation.Value And Not Target.HasFormula And Not IsError(Target.Value2)    PreviousEvents = Application.EnableEvents    Application.EnableEvents = False    EventsChanged = True    Application.Undo    Undone = True    If IsError(Target.Value2) Or Target.HasFormula Then        OldValue = ""    Else        OldValue = CStr(Target.Value2)    End If    If Not ValidChoice Then        Failure = "Choose one city from the drop-down list. The previous selections have been kept."        GoTo Finished    End If    'The separator cannot also appear inside an individual list item.    If InStr(1, NewValue, Separator, vbBinaryCompare) > 0 Then        Failure = "This item contains the selection separator. Choose a separator that is absent from the list items."        GoTo Finished    End If    If Len(OldValue) = 0 Then        Result = NewValue    Else        If Not AllowRepeats Then            For Each Entry In Split(OldValue, Separator)                If StrComp(CStr(Entry), NewValue, vbTextCompare) = 0 Then                    AlreadySelected = True                    Exit For                End If            Next Entry        End If        If AlreadySelected Then            Result = OldValue        Else            Result = OldValue & Separator & NewValue        End If    End If    If Len(Result) > 32767 Then        Failure = "The selections would exceed Excel's cell text limit. Clear this cell and start a shorter list."        GoTo Finished    End If    Target.Value2 = ResultFinished:    If EventsChanged Then Application.EnableEvents = PreviousEvents    If Len(Failure) > 0 Then MsgBox Failure, vbExclamation, "Multiple selections"    Exit SubFailed:    Failure = "The selections could not be combined: " & Err.Description    'Undo may be unavailable for a programmatic edit. Do not undo again.    On Error Resume Next    If Undone Then Target.Value2 = OldValue    If EventsChanged Then Application.EnableEvents = PreviousEvents    On Error GoTo 0    MsgBox Failure, vbExclamation, "Multiple selections"End SubPrivate Function HasListValidation(ByVal Cell As Range) As Boolean    On Error Resume Next    HasListValidation = (Cell.Validation.Type = xlValidateList)    On Error GoTo 0End Function

VBA example by Sumit Bansal, https://trumpexcel.com.

This is a worksheet event: Excel runs it when you change the selection cell. Put it in that worksheet’s code window, not a standard module, and don’t press F5 to run it.

Here are the steps to add it to your workbook:

Press Alt + F11 to open the Visual Basic Editor.

The Visual Basic Editor and Project Explorer for the example workbook.

You can also open the editor from the Developer Tab by clicking Visual Basic. If the Project Explorer pane is missing, press Ctrl + R to show it.

In Project Explorer, expand your workbook’s Microsoft Excel Objects folder.

The example workbook expanded in Project Explorer with its worksheets under Microsoft Excel Objects.

Double-click the worksheet that contains the drop-down list.

The empty No Duplicates worksheet code window.

Paste the complete code into that worksheet’s code window.

The complete worksheet event code installed in the No Duplicates worksheet, showing its opening lines.

If that sheet already has a Worksheet_Change procedure, don’t paste a second one below it. The two pieces of event code need to be combined into one procedure.

Close the Visual Basic Editor to return to Excel.

Cell C2 ready for city selections after returning from the Visual Basic Editor.

Save the workbook as Excel Macro-Enabled Workbook (*.xlsm).

Excel Macro-Enabled Workbook (*.xlsm) selected in the Save As dialog.

The older .xls format can also store VBA, but use .xlsm for this workbook. Saving as .xlsx removes the VBA needed for multiple selections.

When reopening the workbook, enable macros only if you trust the file. If your organization blocks macros, check with your IT team; you don’t need to enable Trust access to the VBA project object model to run this code.

To try the downloaded No Duplicates sheet, clear C2 first because it contains sample selections. Then choose New York followed by York from the drop-down.

New York and York retained as separate choices in C2.

You get New York, York in C2. If you select York again, the value stays the same, because the code compares complete city names rather than looking for one name inside another.

The comparison ignores capitalization, so Boston and BOSTON count as the same choice. Selecting an existing city does not remove it; press Delete to clear the cell and choose the cities again.

The code briefly undoes the new selection to read what was in the cell before, then combines the values. It turns events off while writing the result so that its own change doesn’t trigger the same code again.

Important: This macro uses Excel’s Undo history internally, so you cannot rely on Ctrl + Z to undo its changes. Keep a copy of the workbook before making substantial changes.

Use the drop-down to add one city at a time. Typing one valid city also works, but replacing the cell with a whole comma-separated list isn’t the same as making individual selections.

Ordinary paste can replace the cell’s Data Validation rule and stop the multiple-selection behavior. Use Paste Values for a single choice; the code rejects a value outside the list while keeping your previous selections.

Changes to several cells at once are left as ordinary edits, rather than being appended. If other VBA writes to these cells, that code must disable events around its writes because programmatic changes don’t create the same Undo history as your selections.

Allow Repeated Selections

The Allow Repeats sheet has the repetition version already set up in C2. Note that if you select an item more than once, it will be entered again (repetition is allowed).

Below is the same city list, with the repeated choices kept together in C2.

The Allow Repeats example with C2 cleared before selecting cities.

In the worksheet code, replace the AllowRepeats line with this one:

Const AllowRepeats As Boolean = True

Const AllowRepeats As Boolean = True

Keep the rest of the code, including the helper function, unchanged. Use this setting in the existing procedure rather than adding another copy of Worksheet_Change.

Clear C2 on the downloaded sheet before trying it, because the cell already contains sample selections. Then selecting Boston, Austin, and Boston again gives you Boston, Austin, Boston.

Boston, Austin, and Boston retained together in C2.

Follow along with the example file

Multiple Selections in Excel Drop Down List.xlsm

Use Multiple Selection in Other Cells

Since I get a lot of similar questions, I have created the following sections to answer some of the most asked questions about this tutorial and the VBA code.

If you have any questions, I request you to go through these sections first.

The code starts with C2 as its target. On the Several Cells sheet, C2, C3, and C4 each keep their own selections from the city list.

Cells C2, C3, and C4 contain independent selections from the same city list.

To use those three cells, replace the InputCells line near the top of the code with:

Const InputCells As String = "C2:C4"

Const InputCells As String = "C2:C4"

Make sure each target cell also has a List validation rule. Changing this line tells the macro where to work; it doesn’t create the drop-down lists.

You can use the following range variations in your own workbook. Replace the same InputCells line with the one you need, rather than adding all of them.

For every list-validation cell in column C:

Const InputCells As String = "C:C"

Const InputCells As String = "C:C"

For columns C and D:

Const InputCells As String = "C:D"

Const InputCells As String = "C:D"

For row 2:

Const InputCells As String = "2:2"

Const InputCells As String = "2:2"

For rows 2 and 3:

Const InputCells As String = "2:3"

Const InputCells As String = "2:3"

Cells without List validation are ignored, even if they’re inside the specified range. Each worksheet that needs this behavior must have its own event code.

These examples accumulate choices within each cell. If you want a choice used in one cell to disappear from other cells’ lists, see Creating Multiple Drop-down Lists in Excel without Repetition.

Change the Separator or Put Each Selection on a New Line

The comma examples use a comma followed by a space between choices. The Separator setting controls both how values are joined and how the no-duplicates version identifies individual choices.

To use a vertical bar followed by a space, replace that setting with:

Const Separator As String = "| "

Const Separator As String = "| "

You can use a space alone with a list of single-word choices:

Const Separator As String = " "

Const Separator As String = " "

A space alone won’t work with this example’s New York entry because that city already contains a space. Choose a separator that appears in none of your list items; the code rejects a choice containing the separator.

Clear any existing selections before changing the separator. Otherwise, the old text still uses the previous separator, so the code cannot reliably split it into individual choices.

For a new line in the same cell, use the Line Breaks sheet. Below is the city list with selections displayed on separate lines in C2.

New York, York, and Boston displayed on separate lines in C2.

In that sheet’s code, the separator is:

Const Separator As String = vbLf

Const Separator As String = vbLf

vbLf supplies the line-feed character Excel uses inside a cell. The original example used vbNewLine; this version uses vbLf to insert the Excel cell line break directly.

If you’re adding this version to your own sheet, make the line breaks visible:

Select the cell containing the selections.

Cell C2 selected before enabling Wrap Text.

On the Home tab, click Wrap Text.

Wrap Text on the Home tab of the Excel ribbon.

Increase the row height so that all the selected cities are visible.

The increased row height makes all selected cities visible in C2.

The example file reserves enough height for all five cities. Selecting New York, York, and Boston puts each city on its own line instead of separating them with commas.

Use the Drop-Down on a Protected Sheet

You can keep the city source list protected while allowing selections in C2. The Protected Sheet example does this by leaving C2 unlocked before protecting the worksheet.

The protected-sheet example keeps the selected cities in editable cell C2.

For your own worksheet, set up the drop-down and event code before applying protection. Then follow these steps:

Select the cells that should remain editable, such as C2.

Cell C2 selected as the input that will remain editable.

Press Ctrl + 1 to open Format Cells.

The Format Cells dialog opened for C2.

On the Protection tab, clear the Locked checkbox.

The Locked checkbox cleared on the Protection tab for C2.

Click OK.

Cell C2 after its Locked setting has been cleared.

On the Review tab, click Protect Sheet.

The Protect Sheet dialog with selection of unlocked cells allowed.

Choose your protection settings and click OK, confirming your password if you set one.

The city drop-down remains available in unlocked C2 on the protected worksheet.

The macro writes only to the unlocked selection cell, so this version doesn’t need Me.Protect UserInterfaceOnly:=True. The protected example was tested again after closing and reopening the workbook.

The example sheet uses demo as its demonstration password. If you need to change its source list, unprotect the sheet first; for more detail on choosing editable cells, see Lock Cells in Excel.

Follow along with the example file

Multiple Selections in Excel Drop Down List.xlsm

You can now keep several choices in one cell, with or without repeated items. Save the workbook as .xlsm so the code stays with it when you reopen it.

Other Excel Articles You May Also Like:

Sumit Bansal

13x Microsoft Excel MVP

Hey! I'm Sumit Bansal, founder of trumpexcel.com and a Microsoft Excel MVP. I started this site in 2013 because I genuinely love Microsoft Excel (yes, really!) and wanted to share that passion through easy Excel tutorials, tips, and Excel training videos. My goal is straightforward: help you master Excel skills so you can work smarter, boost productivity, and maybe even enjoy spreadsheets along the way!

LinkedIn YouTube X (Twitter)

601 thoughts on “How to Make Multiple Selections in a Drop Down List in Excel”

Jasmine

August 2017 at 2:12 am

Hello, Both of the codes worked great! However I am trying to combine both of the codes in the same file (workbook). One column I need to select multiple item with repetition and the other I need to select multiple items without repetition. How do I combine these?

Private Sub Worksheet_Change(ByVal Target As Range)

‘Code by Sumit Bansal from https://trumpexcel.com
‘ To Select Multiple Items from a Drop Down List in Excel

Dim Oldvalue As String
Dim Newvalue As String

On Error GoTo Exitsub
If Target.Column = 9 Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = “” Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = “” Then
Target.Value = Newvalue
Else
Target.Value = Oldvalue & “, ” & Newvalue
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

and this one

Private Sub Worksheet_Change(ByVal Target As Range)
‘Code by Sumit Bansal from https://trumpexcel.com
‘ To Select Multiple Items from a Drop Down List in Excel
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Column = 15 Or Target.Column = 16 Or Target.Column = 9 And Target.Row > 1 And Target.Row < 3000 Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = "" Then
Target.Value = Newvalue
Else
If InStr(1, Oldvalue, Newvalue) = 0 Then
Target.Value = Oldvalue & ", " & Newvalue
Else:
Target.Value = Oldvalue

End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
Reply
Ben Muir

August 2017 at 10:59 pm

I tried to use your code and I am getting my list from sheet 2 and I can get the drop down to allow me to pick one name but I am unable to to pick more than one. I assume it has to do with the way I am tying is into the sheet 2. Should I be using the Target.Address = “$F$6” or Sheet2!A1:A12 where my list is.
Thanks for the help

Private Sub Worksheet_Change(ByVal Target As Range)

‘Code by Sumit Bansal from https://trumpexcel.com
‘ To Select Multiple Items from a Drop Down List in Excel

Dim Oldvalue As String
Dim Newvalue As String

On Error GoTo Exitsub
If Target.Address = “$F$6” Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = “” Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = “” Then
Target.Value = Newvalue
Else
Target.Value = Oldvalue & “, ” & Newvalue
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

End Sub
Reply
- Sumit Bansal
  
  August 2017 at 12:17 pm
  
  Hello Ben.. The code needs to be in the sheet in which it’s supposed to run. In this case, if you multiple selection to work in Sheet 2, you need to place the code in Sheet2 code window in VBA backend.
  Reply
Beau Ties Ltd of Vermont

August 2017 at 10:04 pm

This was working great and it just stopped working suddenly. Not sure why.
Reply
- Beau Ties Ltd of Vermont
  
  August 2017 at 10:12 pm
  
  Never mind, figured it out. Macro security, make sure after an office 365 update you turn your macro security trust back off otherwise it might block this code.
  Reply
Manisha Mehra

August 2017 at 4:27 pm

Can we put multiple VBAs in the same sheet for different cells having separate dropdown lists.. I tried it does not work.
Reply
- Sumit Bansal
  
  August 2017 at 12:20 pm
  
  Hey Manisha.. For this to work for different cells, you can simply modify the following line: If Target.Address = “$C$2”
  
  For example, if you want this to work for C2 and D2, make it –
  If Target.Address = “$C$2” Or Target.Address = “$D$2”
  
  And yes, you can also add mulriple VBA codes as well, however, it may not be necessary in this case.
  Reply
Dorsey

August 2017 at 8:40 am

I followed the instructions. How do I apply it to C2:C42? When I scroll down in my worksheet, the drop down box loses the ability to select more than one option.
Reply
- Sumit Bansal
  
  August 2017 at 12:44 pm
  
  Hello Dorsey.. To make it work for multiple cells (C2:C4), replace the following line in the code:
  If Target.Address = “$C$2”
  
  with this line:
  If Target.Address = “$C$2” Or Target.Address = “$C$3” Or Target.Address = “$C$4” Then
  
  If you want it to work for the entire column C, use the following line:
  If Target.Column = 3
  Reply
Sue Retzer

August 2017 at 12:21 am

I had it working then it stopped. I need to be able to select from a list into a full column.
Followed the direction, just changed the line If Target.Address = “$F$6:$F$156” Then

In the target column did the Data -> Data validation -> List to the column holding the list (X1-155)

What am I missing?
Reply
Momo

August 2017 at 2:38 am

Hello, using a VBA code similar to yours, I am selecting multiple items in a drop down list that are separated by comma. The code I am using is to edit and add multiple items in a drop down in the same cell. I am trying to create a pivot table with independent filters instead of all the line items in each cell. For example, in my drop down list, in one cell, I selected apple orange and banana, and in another cell, I have kiwi orange and banana, however, I just want to focus on the banana independently that occurred each time. Is this possible at all? Or will I have to resort to traditional excel and create a cell for every single item. (Really what I am doing is monitoring donor/patient reactions, so I am selecting for example, nausea, loss of consciousness, etc, and where the conditions were mild, moderate, and severe and how many times in a mild reaction did a donor have nausea symptoms or something). Hope there’s something to do this! I just like that the drop down feature makes one column instead of a million different columns but I really need it to analyze my data. Thanks!
Reply
- Big Daddy D
  
  August 2017 at 3:17 am
  
  I have the same exact question.
  Reply
  - momo
    
    August 2017 at 3:59 am
    
    i’ve been looking everywhere for a solution!! I even asked on the microsoft excel community for help.. maybe there will be a glimpse hope for us!! lol!
    
    https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_winother-mso_2013_release/help-how-do-i-create-a-pivot-table-to-filter/9f274fe3-8dba-4b95-9df0-346e8cbe1502
    Reply
Katie Axtell

August 2017 at 7:37 am

Hello Sumit,

This is an excellent layout and step by step instruction. Thank you so much! I have been trying to change the code to where the multiple list selections appear unduplicated across the row in separate columns. Do you have the VBA code know where in the above listed one I can edit to make that happen?

Thank you in advance!
Reply
Silroy

August 2017 at 6:31 pm

Hi Sumit .. Thanks a ton for sharing this .. the code works fine for me 🙂 on a unprotected sheet. It stops the moment the sheet is protected .. my apologies if this has already been answered as i am unable to find any threads on password related issue for this VBA command .. Please help!!
Reply
Shaniko Nichol-Driskill

July 2017 at 10:35 pm

I used the code above to allow for multiple selections, and then I used the modification from Sumit Bansal to modify it work for all of the drop downs in my worksheet. This worked great! Thanks! Now my problem is that in the header row of my table I can’t make any changes. Any time I try to make changes the text keeps multiplying instead of deleting. I am thinking that this might have to do with the fact that I had converted this spreadsheet to a table before I add the dropdowns and code. So the header row had it’s own built in sorting/filtering dropdowns that the code may be messing with?? Regardless it is huge table and now I am not sure what to do with it, and would appreciate any suggestions. My header row is mess and everything I try to do to fix it is making it worse.
Reply
Shaniko Nichol-Driskill

July 2017 at 1:29 am

Help! I used your code for being able to do multiple selections from a drop down menu, and then I modified it to apply to all the drop downs in my worksheet as described below. This worked great. Now I am having this weird problem where if I try to delete and edit something in a cell that does NOT have a drop down it won’t allow me to delete. So every time I try to delete and add, I just end up with more and more copies of almost the same thing. The only thing I can think of is that it is somehow related to the code allowing for multiple selections. Have you ever seen this? Do you know what it might be? https://uploads.disquscdn.com/images/4a019783ca43d6f04d60ef16990d45dd38f33690231d5cf6678d750a7576b04a.png
Reply
JW

July 2017 at 2:57 am

Hi – thanks for providing the VBA code, seems to work great. Is it possible to limit the selections to a certain number? I.e. 5 choices w/the option of selecting a maximum of 3 without repetition. Thanks, I look forward to hearing back from you.
Reply
Jim Baines

July 2017 at 2:14 am

Thank you Sumit for your tutorial for setting up Multiple Selection Drop Down lists…it worked very well and was very helpful! I would however like to have the multiple selections I make from the Drop Down List I created in in Column 3-Row 5, to be displayed down in Column 2- Rows 5 thru 10 instead of side by side in the same cell separated by a comma…I can’t seem to find an example of the VBA Code I could use to accomplish this…Can you please provide an example for this scenario? Thanks!
Jim B.
Reply
Evan Albrighton

July 2017 at 6:00 pm

Hi, I’ve now made a list using this code. Now some cells have multiple entries but I am struggling to find a way to filter specific words in the boxes with multiple. The standard filter just showed everything in the box and advanced custom filter only allows you to filter 2 words/ phrases. So could someone help me find a way to search a list for cells containing key words/ phrases
Reply
- momo
  
  August 2017 at 1:16 am
  
  hi, did you ever figure out how to do this? i’m trying to create a pivot table based on certain values in the cell even when there are multiple options selected and i can’t find a solution. :/
  Reply
- Jose
  
  January 2019 at 8:48 am
  
  Same
  Reply
Richard Abell

July 2017 at 4:38 pm

Hi, This is exactly what I need, followed all your instructions and even downloaded your sample file, unfortunately it just is not working, I have changed the settings to allow VBA projects, am I missing something!
Reply
nat

July 2017 at 9:20 pm

thanks so much! it works beautifully!
Just had one problem: why does it refrain me from editing other cells?
Thanks!
Reply
Triuwida

July 2017 at 7:00 pm

Rather than have the output read “one, two, three” I would like it to read “one, two and three”. Or “one and three” etc. Is this doable?
Reply
Brittany Argotsinger

July 2017 at 2:27 am

Is there a way to tweak your code so that each drop down selection appears on a new line within the cell (rather than separated by commas)?
Reply
Brittany Argotsinger

July 2017 at 4:11 am

What if my dropdown lists are in a different sheet than the dropdown data?
Reply
T.L.

July 2017 at 5:17 am

Hi, thanks for the great code. I have it working on 4 separate columns in a file I have, but there is a strange issue I’m seeing. If I enter data in a cell directly in front of one of the columns I have the VBA code running against and then either tab into or right arrow into the coded column, the cell highlight will jump back to the cell I came from. It then also runs the VBA code against that cell now too. Example: column B has the VBA code applied to it, column A does not. If I enter any data into column A, press TAB to go to the next cell in the row which is in column B. The cell highlight moves to column B, briefly, then jumps back to column A. If I had for example entered “1” in the cell in column A prior to pressing TAB, if I then enter “2” in that same cell, the VBA code will make the data in the column A cell be: “1, 2”. Any clue on what I can change in the code to keep this from happening? Here’s my version of the code posted here, which is based on the code posted for keeping duplication of choices from happening. It looks for column headers by title to determine which columns to run the script on.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

‘Code by Sumit Bansal from https://trumpexcel.com
‘ To Select Multiple Items from a Drop Down List
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Cells(1, ActiveCell.Column).Value = “Vendor Type” Or _
Cells(1, ActiveCell.Column).Value = “Types of data shared with Vendor” Or _
Cells(1, ActiveCell.Column).Value = “Data Transferred” Or _
Cells(1, ActiveCell.Column).Value = “Audit Artifacts Received” Then

If Oldvalue = “” Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = “” Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = “” Then
Target.Value = Newvalue
Else:
If InStr(Oldvalue, Newvalue) = 0 Then
Target.Value = Oldvalue & “, ” & Newvalue
Else:
Target.Value = Oldvalue
End If
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
Reply
- T.L.
  
  July 2017 at 11:30 pm
  
  In case anybody has this same problem I figured out a different way to code this and it solved the problem:
  
  Option Explicit
  
  Private Sub Worksheet_Change(ByVal Target As Range)
  
  ‘ To Select Multiple Items from a Drop Down List
  Dim Oldvalue As String
  Dim Newvalue As String
  Dim a As Long
  Dim b As Long
  Dim c As Long
  Dim d As Long
  
  ‘ Set the header values we’re looking for in the sheet
  a = WorksheetFunction.Match(“Vendor Type”, Sheet1.Range(“A1”, Sheet1.Range(“IV1”).End(xlToLeft)), 0)
  b = WorksheetFunction.Match(“Types of data shared with Vendor”, Sheet1.Range(“A1”, Sheet1.Range(“IV1”).End(xlToLeft)), 0)
  c = WorksheetFunction.Match(“Data Transferred”, Sheet1.Range(“A1”, Sheet1.Range(“IV1”).End(xlToLeft)), 0)
  d = WorksheetFunction.Match(“Audit Artifacts Received”, Sheet1.Range(“A1”, Sheet1.Range(“IV1”).End(xlToLeft)), 0)
  
  Application.EnableEvents = True
  On Error GoTo Exitsub
  If Target.Column = a Or _
  Target.Column = b Or _
  Target.Column = c Or _
  Target.Column = d _
  Then
  
  If Oldvalue = “” Then
  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
  GoTo Exitsub
  Else: If Target.Value = “” Then GoTo Exitsub Else
  Application.EnableEvents = False
  Newvalue = Target.Value
  Application.Undo
  Oldvalue = Target.Value
  If Oldvalue = “” Then
  Target.Value = Newvalue
  Else:
  If InStr(Oldvalue, Newvalue) = 0 Then
  Target.Value = Oldvalue & “, ” & Newvalue
  Else:
  Target.Value = Oldvalue
  End If
  End If
  End If
  End If
  End If
  
  Application.EnableEvents = True
  Exitsub:
  Application.EnableEvents = True
  End Sub
  Reply
Lisa Wrona

July 2017 at 1:26 am

Hello. For some reason I can not get the code to work. I copied and pasted the same way you did but it still does not work. What am I doing wrong??
Reply
- Brittany Argotsinger
  
  July 2017 at 4:12 am
  
  I am having the same issue. Do I need to modify any of the code for my dropdown cells?
  Reply
  - Brittany Argotsinger
    
    July 2017 at 4:16 am
    
    Yes, yes I did. I substituted $C$2 for the correct cells in my sheet, and it worked! I am new to VBA, so I’m sorry for the silly question. Thanks for the code!
    Reply
Ashley Curiel

July 2017 at 11:08 pm

@sumitbansal23:disqus Hi Summit, your code worked great for non repetition. I noticed the values selected appear in cell window. Besides that feature is there a way to keep the chosen values from the dropdown list highlighted and erase selected value by double clicking on them? Ultimately I am following up with a code that will hide selected columns depending on the values appearing in the cell chosen from your code. Any advice on that?
Reply
Maria Hooker

July 2017 at 10:05 pm

I tried to manipulate the code to account for 2 columns to be able to select multiple items but having trouble. Can anyone help?
Reply
farid abaoui urbano

June 2017 at 4:31 pm

Hi, this worked perfectly, thank you.
But I cannot remove any item from the selection unless I delete the entire entry.
Any suggestions ?
Reply
karthi keyan

June 2017 at 1:50 pm

hi sumit,
How to get the selected column header name for the selected cell.
Reply
Andrew Starling

June 2017 at 1:31 am

Hello Sumit,

Is there a way to add a Sum of all the values collected? I’d like to be able to sum the value of all the items selected and then use the sum in another formula.
Reply
pernillepar

June 2017 at 11:56 pm

Dear Sumit, thanks for the great code! I run into a problem when I lock the sheet, then the macro stops. I am going to share my sheet with others and need the lock specific cells. Do you have any suggestions how to solve this problem? Thanks for your time!
Reply
Shawn Cook

June 2017 at 9:27 pm

How do you take the validation out so I can add text to the end of the cell, text that isn’t in the dropdown?
Reply
- Shawn Cook
  
  June 2017 at 9:33 pm
  
  Actually I just removed the error alert and I was able to add more choices to the end but it repeats the choices I made using the code. So if I picked A B C and added lol. It would become A,B,C,A,B,C,lol
  Reply
Viv

June 2017 at 9:44 pm

Is there a way to deselect something from the list if it was clicked by accident?
Reply
- Viv
  
  June 2017 at 9:49 pm
  
  Never mind. Was able to look for a previous comment below
  Reply
tallnewenglander

June 2017 at 8:04 pm

Sumit – thanks for putting this up, this was exactly what I was looking for. One question – once I’ve implemented this on my spreadsheet, I may create pivot tables using the data contained therein. Is there a way to force the pivot table to treat each selection in one cell separately instead of creating a new category defined as all multiple selections?
Reply
- momo
  
  August 2017 at 12:39 am
  
  let me know if you ever found a solution!
  Reply
Linh Cao Ngoc Nguyen

June 2017 at 12:10 am

Hi Sumit, not sure if you are still active, but I want to thank you for this work. You are wonderful, and this material is so helpful.
Reply
- Sumit Bansal
  
  June 2017 at 3:35 am
  
  Thanks a lot for the kind words.. Glad you found it useful 🙂
  Reply
karthi keyan

June 2017 at 12:38 pm

Hi Great Post, Its very helpful,Thanks a lot.
I Want to know is it possible to know the selected items to be highlighted in the drop down if yes please help us to do that. thanks in advance.
Reply
- Sumit Bansal
  
  June 2017 at 3:36 am
  
  Hey Karthi.. I don’t think it’s possible to apply any kind of formatting in the drop down.
  Reply
Erin Bresnahan

June 2017 at 12:56 am

Hello, this is super helpful, thanks! Only thing is I can only get this to work when I use it alone without any other VBA code, but as soon as I use additional code to have my sheet perform other functions, my drop-down list reverts to only accepting one option at a time. Below is the code – any ideas how I can get it all to work? My other two commands are for time stamps in two different places on the same sheet. The other two commands still work when I combine all the code, but the ability to choose more than one option from the drop-down menu stops working.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Or Target.Column > 100 Or Target.Column = 9 Then Exit Sub
Application.EnableEvents = False
Cells(Target.Row, 1) = Now
Application.EnableEvents = True

Dim xCellColumn As Integer
Dim xTimeColumn As Integer
Dim xRow, xCol As Integer
xCellColumn = 8
xTimeColumn = 9
xRow = Target.Row
xCol = Target.Column
If Target.Text “” Then
If xCol = xCellColumn Then
Cells(xRow, xTimeColumn) = Now()
End If
End If

‘Code by Sumit Bansal from https://trumpexcel.com
‘ To Select Multiple Items from a Drop Down List in Excel
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Address = “$W$3” Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = “” Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = “” Then
Target.Value = Newvalue
Else
If InStr(1, Oldvalue, Newvalue) = 0 Then
Target.Value = Oldvalue & “, ” & Newvalue
Else:
Target.Value = Oldvalue
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
Reply
- Sumit Bansal
  
  June 2017 at 3:49 am
  
  I believe these codes are interfering as both sets of codes (your and mine) is fired when you try to use the drop down in W3. What are you trying to get done with your code in the beginning?
  Reply
Daniel Lee

June 2017 at 12:53 am

Hello, The Code works well for the list put in other columns where I have some descriptive copy I have been having issues making changes to the content when this code is present. Have you run into this issue?
Reply
Mary Dronca

June 2017 at 11:05 pm

Hi Sumit, This has been extremely helpful, thank you! I have applied the code for multiple selections to several columns which contain drop down lists. However, every cell in the entire sheet is requiring a complete clearing of contents when an edit is made. This is similar to Emily’s querry, but not quite. Thank you so much for providing this service! -Mary
Reply
Philipp Krohn

June 2017 at 2:43 pm

Hello Sumit, thank you for this expansive tutorial, I used this solution now but have a follow up question: I want to use the multiple entry result cell as input for another list lookup function. E.g. My multiple entry cell looks like “Item 1, Item 4, Item 4, Item 10” so far, so good. The final result in another cell should be the sum of the values of these items ( e.g. 1$+4$+4$+10$ = 19$). Is that possible and if so, how? – Thank you very much for your time!
Reply
Ryan O'Keefe

June 2017 at 3:58 am

Nice. You just turn a “I don’t know if I can do that” into a 2 minute chore. You rock Sumit!
Reply
COURTNEY

June 2017 at 5:39 am

Once you select options, is there a way to edit in Excel? Example – clicked on desired drop down item, but would now like to add additional information into cell. I am getting an error message.
Reply
Bikke Chettri

June 2017 at 4:46 pm

I saved on format as reccomeneded. But when I reopen it it stops working. Any one know why ?
Reply
- momo
  
  August 2017 at 12:37 am
  
  you need to save your excel spreadsheet as a macro enabled spreadsheet. go to file, save as, and then when you see the title, it says save as type … and select excel macro enabled workbook (*.xlsm). now the code will be saved everytime you open and close.
  Reply
  - Panpic
    
    December 2019 at 3:33 pm
    
    Great. Thank you so much. I try to find support this solve
    Reply
Duxqdj

May 2017 at 2:50 pm

@sumitbansal23:disqus Hi, this code is superb, but can you tell me if it’s possible to ensure that once the selections are made they appear in alphabetical order? pleas reply as soon as possible
Reply
vijay raj

May 2017 at 4:33 pm

Hi Sumit, is there a way for to vlookup code for the multiple drop down list selection for the above code
Reply
M.A. Rohani

May 2017 at 1:12 pm

Thanks for the post. What if you have more than one column in the spreadsheet that you want to set up dropdown menu with multiple data point selection?

Thanks
Reply
Dana Bigler

May 2017 at 10:11 pm

I think I have looked through all the comments to find this and don’t see it, how do I point the code to look at a different sheet within the workbook for the list of options? I have created the list on Sheet 2 A2:A27 and the drop down box I need populated are on Sheet 1 C10 &D10 which is a merged cell. I have never done VBA coding so please help. Thank you.
Reply
- Mary
  
  December 2018 at 3:00 pm
  
  I pose same question; did you get an answer
  Reply
- MinBee
  
  July 2020 at 5:05 am
  
  Dan, you don’t do that in the code. When you select your list in data validation you can select a different sheet. When you get into the data validation window you just click the formula area, then click the page, and the cell set you want to use
  Reply
Nel Mag

May 2017 at 9:54 pm

Hi, Sumit. Thanks for the code it works great. I would like to add a carriage return at the end of each selection. Is it possible ? And, is it possible to edit the selection, like adding a person name in the selection. ex: John needs to walk 15 minutes twice a day ? Tanks.
Reply
Babobim Thip-utai

May 2017 at 2:36 pm

Wonderful! Thank you!
Reply
Andrei Caraus

April 2017 at 7:28 pm

You’re amazing ! Thank you so much!
Problem: I have a excel file that i must send to many of my colleagues, my question is, how can i send it with the code so they wont have to paste it in VBA?
Thanks !
Reply
- Sumit Bansal
  
  April 2017 at 8:00 pm
  
  You paste the code in your file and save it with .xls or .xlsm format. Now you can send it to others and the code would work
  Reply
  - Andrei Caraus
    
    April 2017 at 4:08 pm
    
    Thanks for the quick reply !
    Using XLS or XLSM format does not work for me but i succeeded using XLSXM format.
    Thank a lot !
    Reply
Anna

April 2017 at 8:32 pm

Hi, I’ve tried everyone’s version of this VBA code and no one’s is working. I have created the data validation drop down list, ez pz, but regardless of the code I paste in, I don’t get these results. I am using Excel 2013 and the worksheet/book isn’t protected. I am simply trying to get multiple choices in the same cell.
Reply
- Sumit Bansal
  
  April 2017 at 8:01 pm
  
  The code I have provided above only works for cell C2. You will have to change the line Target.Address = “$C$2” to make it work for your cell.
  Reply
  - Anna
    
    April 2017 at 10:05 pm
    
    Yeah, I read through a million of these comments and saw that, which I changed of course. Turns out, I needed to save and exit for the code to run and work. Strange!
    Reply
  - Brittany Argotsinger
    
    July 2017 at 4:29 am
    
    What do I use for a range of cells in place of $C$2?
    Reply
Heather Reisig

April 2017 at 11:41 pm

Dear Sumit,
You are THE man. Thank you.
That is all.
Reply
- Sumit Bansal
  
  April 2017 at 8:01 pm
  
  Thanks for commenting Heather! Glad it helped 🙂
  Reply
Lara Mackenzie

April 2017 at 3:49 pm

Your posts are fantastic Sumit. Thanks. Can you help me with this issue. I have created the dropdown list with multiple entries without repetition and would like to know if each selection can be on a separate line in the cell and not separated by a , (comma). Your help would be appreciated and I am generally useless at VBA, but your info and tutorials have been super helpful.
Reply
- Sumit Bansal
  
  April 2017 at 8:06 pm
  
  Glad you found the tutorial useful Lara.
  
  To get each selection in a new line, replace this line in the code (Target.Value = Oldvalue & “, ” & Newvalue) with this line of code (Target.Value = Oldvalue & vbNewLine & Newvalue)
  Reply
  - Lara Mackenzie
    
    April 2017 at 9:14 pm
    
    You are a star…. thank you. Your help is amazing.
    Reply
  - Lara Mackenzie
    
    April 2017 at 9:44 pm
    
    This worked really well, thanks. Can you tell me how to delete an entry that is selected in error? I dont want to have to delete all the entries already selected, only one or two. If I remove all the entries in the cell, then I have to reselect all the ones I need in the cell. Thanks. 🙂
    Reply
    - David Hilyard
      
      October 2017 at 3:42 am
      
      Sumit – I’ve used your code to set up drop down lists in three separate columns, without repetition. It works like a Boss. Now, Like Lara above, I would also like to delete accidental entries. Some code from another source was supplied in the discussion above, but it doesn’t do the line breaks. I’m a complete beginner with VBA, and can’t see how to adapt one to the other. Any advice? Thanks.
      Reply
  - David Hilyard
    
    October 2017 at 2:05 am
    
    Sumit – this is brilliant! It has really helped me. Thank you.
    Reply
  - Michelle
    
    December 2017 at 1:11 am
    
    This was helpful, but is there a way to have this AND have code so that the duplication in the cells stopped? I was using Spyrule’s code he posted above, which is:
    If Oldvalue = “” Then
    Target.Value = Newvalue
    
    ElseIf Target.Value = Oldvalue Then ‘<~~~ This prevents self-duplication.
    GoTo Exitsub
    
    Else
    Target.Value = Oldvalue & ";" & Newvalue
    End If
    but is there a way to combine both these things? I've tried combining these lines with this NewLine code but haven't been able to achieve both happening. I know almost nothing about code. Any help is much appreciated!
    Reply
TJ

April 2017 at 12:30 am

Hi Sumit,
Great code here. The problem is (keep in mind it may be on my end), the code is not working. I even downloaded your code, and tried running it on my machine, and I still am only able to select one of the drop list items. Is it possible there is a security setting I missed? I have enabled macro’s, and allowed trust access to VBA project object model, but again, the code doesn’t work. Please help. Also, is it possible, to have the code work on several different cells (copy and paste drop down) and have the VBA code changed to accommodate? I am trying to make a drop down selection list down over 260 line selections. I essentially need to copy the drop down to that many, and allow for selection as I go through the line items. Thanks again.
Reply
- Sumit Bansal
  
  April 2017 at 7:58 pm
  
  Hello TJ.. The code I have given is made to work on cell C2 only. You can easily make it work for more cells for rows/columns. For example, to make it work for entire column C, use target.column = 3
  Reply

Leave a Comment Cancel reply