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”

Mohammed Khaja Hussain

August 2018 at 9:08 am

Hi,
i have read your drop-down multiple selection post (without repetitions). it worked perfectly fine,but whenever i am closing worksheet all data validation get deleted how to solve this problem.
thank you in advance.
Reply
Olivia Kebble

August 2018 at 9:08 am

Hello,

When making multiple selections, how do you then delete a selection you may have accidentally clicked?
Reply
- superloid
  
  August 2018 at 10:41 am
  
  that is also what I was going to question. Only deleting entire value works currently.
  Reply
Mahesh Babu

August 2018 at 9:08 am

Hi,
If the sheet is protected, multiple drop down list is not working ? any thoughts please
Reply
Lucas Vieira

August 2018 at 9:08 am

edited code so that the list removes a duplicate element if that happens: https://pastebin.com/N9RGFkBZ
Reply
- Jamie
  
  January 2019 at 5:10 am
  
  AWESOME! This deletes the unwanted list item via “reselecting it” in the dropdown. Thank you!
  Reply
  - Kristaps
    
    February 2020 at 6:31 pm
    
    This worked great for me
    Reply
Kielce Gussie

August 2018 at 9:08 am

I plug in the VBA code and changed it to If Target.Column = H, but it does not work. I still cannot select multiple answers.
Reply
- JMB
  
  September 2018 at 5:50 pm
  
  Hi – you need to replace H with the number of the column which in this case is 8
  Reply
Chris Caesar

August 2018 at 9:08 am

Hi, I recently changed the Columns numbers in the VBA code because the applicable Columns changed location and now I’m getting compile errors. However, it worked just fine before I changed the column numbers. After the error it highlights the “.Column” portion of the first “If Target.Column” instance. Not sure why I’m getting this error. I had changed the VBA column numbers several times before this while creating the Workbook and it worked fine, but something is messed up now. Please help.
Reply
Dan

August 2018 at 9:08 am

This code worked for a moment yesterday, then my file crashed and the code has not worked since… Incredibly frustrating…
Reply
Tanya Sawrie

August 2018 at 9:08 am

Is there a way to count the items later on if they are listed in a multiple list?
Reply
- Jose
  
  January 2019 at 8:28 am
  
  If you know please share
  Reply
  - Jamie
    
    January 2019 at 5:16 am
    
    =IF(C3=””,””,(LEN(TRIM(C3))-LEN(SUBSTITUTE(TRIM(C3),”,”,””))+1))
    Reply
    - Jamie
      
      January 2019 at 5:23 am
      
      (You put that formula in a separate cell)
      Reply
Madhur

August 2018 at 9:08 am

Hi Sumit

I have two queries.
1. I tried your solution and put the list in the same sheet (A3-A5) as the cell (E3) where I wanted the multiple selection, but its not working for some reason. It is just picking 1 value. The sheet is IssueLog.
2. I have the Standard list in one Worksheet ‘ReferenceInfo’ and I want to implement the multiple selection in another worksheet ‘IssueLog’ in column E (E3 to E300). Can it be done?
Reply
Michael Babines

August 2018 at 9:08 am

I downloaded the sample to select multiple items. When selecting items, only one displays. What needs to be done to activate the display of multiple items?
Reply
Justi

January 2018 at 6:03 pm

I have to add multiple questions some that requires only one answer and other multiple. How can you create list without all of them being multiple selection.
Reply
- Sumit Bansal
  
  February 2018 at 12:01 pm
  
  You will have to change the code so that only the cells where you want multiple selections are included in the code
  Reply
Justi

January 2018 at 5:16 pm

I have just made my excel worksheet Marco enabled and added the code to the code window saved and closed. I went to select multiple choices from the list and still can only select one

Private Sub Worksheet_Change(ByVal Target As Range)
‘Code by Sumit Bansal from https://trumpexcel.com
‘ To allow multiple selections in a Drop Down List in Excel (without repetition)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Address = “$C$2” 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
SmrutiS

January 2018 at 4:56 pm

hi Sumit,
can you clarify the following. I have my spreadsheet with multiple columns on sheet 1 and the dropdown list choices which is to be used for column F (on sheet 1) is listed on Sheet 2. Would I paste in the code above on the VBA Editor sheet 1 or 2?
Reply
- Sumit Bansal
  
  January 2018 at 5:00 pm
  
  Hey Smruti, you need to paste the code for the sheet that has the drop-down list. So in your case, it would be sheet 1
  Reply
  - SmrutiS
    
    January 2018 at 5:06 pm
    
    thank you, it worked!
    Reply
Dirk Dinnewet

January 2018 at 7:16 pm

Hello, sorry if this question has been asked before. How about selecting multiple entries in the dropdown listbox ?
Something like ctl + click or so to select multiple entries at once.

Tia.

/Dirk
Reply
Rohit panwar

January 2018 at 3:12 am

I’m really thankful to you as this code too useful for me, but i have a one query when my sheet is protect then this code isn’t work.
Please help me out i just want to use this code if my sheet is protect with password.
Reply
Govardhan

January 2018 at 6:19 pm

Hi Sumit, @sumitbansal23
Its very useful tutorials from your website.

I have used one of your https://trumpexcel.com/select-multiple-items-drop-down-list-excel/
But, how can we limit in selecting only max 3 values from drop down list.

Please help.
Reply
@lex

January 2018 at 12:10 pm

HI, Happy 2018!! I downloaded the example file but it is not working. Any security settings in Excel that I must change? Thank you.
Reply
Poornima Iyengar

January 2018 at 11:04 am

If I have to use the code for two columns specifically, say column D and
column I which have different sets of values in the respective drop down lists, how will the code change? This is the code from your website I am using now:
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 in Excel
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
‘If Target.Address = “$F” Then
If Not Intersect(Target, Range(“I:I”)) Is Nothing 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
Allison Zagiel

January 2018 at 10:28 pm

I saw code to get this to work while the sheet is protected, and that worked great. However, when I shared the workbook it stopped working and kept giving debug pop-ups. Is there a way to use this function while sharing a protected workbook?
Reply
Kris Hamilton

January 2018 at 1:26 am

I have indirect lookups off the back of a drop down and if i choose multiple options in the first cell then my lookup no longer works, is there a solution to this?
Reply
Azim Boblai

December 2017 at 1:15 am

Hi Sumit

How do I protect the code form getting change by other user
Reply
Alec Beckman

December 2017 at 3:11 am

This is a little bit late, but I just came across this and have a couple questions. Is there a way to limit the amount of selections one can make in a drop down list to say, 5? Currently there’s roughly ~200 items in the list and I only want them to be able to select a maximum of 5 of those at a time. Also, say I have the same list in all of column 7 and 8, would I be able to have a command that tells the user that once something is selected in column 7 they can no longer select it in column 8 or vice versa?

Thanks!
Reply
Kristi Matthews Ward

December 2017 at 9:00 pm

Hi Sumit – Thank you for this great information! I have an additional question… We have a worksheet with many picklists, and I am using this code for all multi-select picklists. We do allow the user to enter a new value if needed. Then we use “circle invalid data” to find the new values that have been added. However, when using “circle invalid data” on the multi-select picklists, it ALWAYS circles the cell if there is more than one value entered, even if they are valid choices. Is there a way to get “circle invalid data” to work properly with the multi-select columns? If not, is there something we could add to the code, to “highlight” those values that the user added (that are not valid)?
Thanks so much for your help!
Reply
Alessandro Renzi

December 2017 at 3:54 am

I’m curious on how one can create dynamic multiple items list. For example… let us say we have the following:

Column 1 Drop Down
Colors: Blue, Yellow

Column 2 Drop Down
Blue: Light Blue, Medium Blue, Dark Blue
Yellow: Light Yellow, Medium Yellow, Dark Yellow

Column 2 is dependent to Column 1.

Thus, if I pick “Blue” in column 1 then in column 2 I have the choice to pick Light Blue and/or Medium Blue and/or Dark Blue

If I picked “Blue” and “Yellow” in column 1 then in column 2 my options to pick are:
Light Blue and/or Medium Blue and/or Dark Blue and/or Light Yellow and/or Medium Yellow and/or Dark Yellow

Thank you.
Reply
Vicky McCabe

December 2017 at 6:22 pm

Thanks so much! Really useful, and just saved me hours of brain hurt!
Reply
Mana Fazel

December 2017 at 6:04 pm

Hi there!

First off, many thanks for the code, it made my research that much easier! I was just wondering whether this will be compatible when computing statistical analysis in Studio R; can I conduct tests on excel cells with multiple items?

Thanks in advance,
Mana
Reply
ELISABETH

November 2017 at 10:49 pm

Hello,
Thanks you for this code, it works fine !
I used it on an entire column, and I want to filter by choice (say if i choose “one, two, three”, I want to that cell to come up if I filter for “two”). Is that possible ?
Reply
TL

November 2017 at 9:15 pm

Every time I save the workbook, my coding disappears when I reopen the document. Can someone please help???
Reply
prem parth

November 2017 at 9:51 pm

Thanks for the solution..its works fine…however it is being applied to all the cells in the sheet and I am not able to edit the cells even where there is no drop down menu to choose from. Can i choose the columns to which this code should be applicable?
Reply
Aviral Mittal

November 2017 at 7:11 pm

This solution is OK, but what if you want to delete values? So https://uploads.disquscdn.com/images/f49a89e68134ae908e184e590a5b4e052ec9548648b51162600b703cceefebff.jpg I have produced an advanced solution.
YOu can dowload the code for free. It uses check-boxes and is far more useful.
http://www.vlsiip.com/exceltips.html
Reply
Aviral Mittal

November 2017 at 1:22 pm

I have now developed the whole VBA solution using Listboxes, uploaded it on my web site:
http://www.vlsiip.com/exceltips.html
https://uploads.disquscdn.com/images/f49a89e68134ae908e184e590a5b4e052ec9548648b51162600b703cceefebff.jpg
Reply
aaron

November 2017 at 3:16 am

I cannot get this to work, even with the file I downloaded. I enabled the macros and still did not work. I am using Excel 2016. Could this be the issue? Do you have a solution?

Thank you
Reply
- Aviral Mittal
  
  November 2017 at 7:37 pm
  
  http://www.vlsiip.com/exceltips.html
  Download it from here, it is free easy to understand. It uses Listboxes and checkboxes. Will enable you to select multiple values from this drop down list menu.
  Reply
mahzuz tom

November 2017 at 7:43 pm

Hi,

Im using the multiple selection dropdown list. I don’t know how to describe but hopefully with my example below will make you to understand :-

In my dropdown list have a several option where each option have their own values.
grape – 4
apple – 3
banana – 2
orange – 1

when i choose in the dropdown for Grape,Banana,Apple the excel will look for the lowest value among the option i had choose. In this case, the excel will find that Banana have the lowest value among the option i had choose hence the value showed up is 2.

How can do that?
Reply
Meer

November 2017 at 10:53 pm

Hi, is there any way to add scrolling feature to the drop down?
Reply
Peter Folmer Hansen

October 2017 at 6:01 pm

https://uploads.disquscdn.com/images/3d702f5a3a9b4018d71bd888549aae80b2837a66664a4dab41f92a0b2b29f6d8.png
Hi, I’ve used the code successfully but now it does not work after an update of Excel version 15.39 (171010). It can only contain one value in the field. Should I change something in the code to make it work again?
Reply
JP

October 2017 at 5:36 pm

Anyway to limit the number of entries? I’d like to only be able to select a maximum of 5 entries. I have a formula in F5 on the sheet that counts the number of separators (I used “;” instead of “, “) that adds 1 since for 5 entries there would only be four semi colons. I want a message box to appear when F5 has a value of 5 and then exit the sub.
Reply
Heather Botefuhr

October 2017 at 4:22 am

Hi I have tried this code and still cannot select more than one drop down from my list. are you able to help.
Reply
dolson57

October 2017 at 1:16 am

Thanks so much very easy to follow and worked the first time.
Well done!
Reply
Laura

September 2017 at 9:42 pm

Is there a way to do this using a key combination instead of a comma to delimit the selections?
Reply
Razaul Karim

September 2017 at 1:21 pm

Dear sir,
Can you help me up how to loop range till 2 to 5000 for below code

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
If Target.Address = “$C$2” Or Target.Address = “$D$2” Then
If Target.Address = “$C$2” Then
Range(“E2”) = Range(“E2”) + Target
ElseIf Target.Address = “$D$2” Then
Range(“E2”) = Range(“E2”) – Target
End If
Target = “”
End If

Application.EnableEvents = True
End Sub

Sub Evenement()
Application.EnableEvents = True
End Sub
Reply
Marcia Cooper Gschwind

September 2017 at 1:49 am

Hi. This has been extremely helpful, but I have my drop down list on one sheet and the cell on another. How do I code for that? I am also trying to use a date picker so that when my teachers click on a cell, they can have a calendar pop up to click on. Can you address that issue or tell me where I can find the answer? Thank you.
Reply
Allen Ibsen

September 2017 at 9:41 pm

If I change the address to
If Target.Column = 1 Then
The function does not work. what might I be doing wrong?
Reply
Karen Curtis

September 2017 at 5:30 am

Hi, I was able to get the code to work, but when an email marco was attached to the spreadsheet it quit working. What have I done wrong?
Reply
Mary

September 2017 at 7:17 am

How can I count the number of each selection? I tried countif but it does not seem to be working.
Reply
Mike Knerr

September 2017 at 8:10 pm

I’m having a difficult time implementing this solution for my particular use case. In my spreadsheet, I am applying data validation on the fly first — in other words, every time I click on a cell in a given range on my sheet “User Lists” it checks the header of that column, looks for that value in the header row on “User Picklists” and then if it finds it it uses the list from that page as the list for data validation on User Lists. Some of the columns need to be Multi-Select though, so once that code block runs, I have used yours immediately below it.

However, it’s not working the way I expect it to even though I left the code almost identical to how you are using it above. The difference is in your sheet, the code fires when I select a value from the list. In my sheet, it fires as soon as I click the cell and doesn’t re-fire when I select the value. I believe this has to do with the other code block above it, but I’m not sure how to make your block re-fire when I select the value. Do you have any tips? See full code below:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.ScreenUpdating = False

Dim ws As Worksheet
Dim RefRng As Range, RngFind As Range, NewRng As Range, hdr
Dim RefList As Range, c As Range, rngHeaders As Range, Msg

On Error GoTo ErrHandling

Set ws = ThisWorkbook.Worksheets(“User Picklist”)

‘only deal with the selected cell(s)
Set NewRng = Application.Intersect(Me.Range(“A12:T101”), Target)
If Not NewRng Is Nothing Then

Set rngHeaders = ws.Range(“A11:ZZ11″)

For Each c In NewRng
c.Validation.Delete ‘delete previous validation
hdr = Me.Cells(11, c.Column).Value
If Len(hdr) > 0 Then
Set RngFind = rngHeaders.Find(hdr, , xlValues, xlWhole)
‘matched header?
If Not RngFind Is Nothing Then

Set RefList = ws.Range(RngFind.Offset(1, 0), _
RngFind.Offset(1, 0).End(xlDown))

c.Validation.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Formula1:=”='” & ws.Name & “‘!” & RefList.Address

End If ‘matched header
End If ‘has header

Next c
End If ‘in required range

‘Multi Select
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Not NewRng Is Nothing 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

Here:
Application.ScreenUpdating = True
Exit Sub

ErrHandling:
If Err.Number 0 Then
Msg = “Error # ” & Str(Err.Number) & ” was generated by ” & _
Err.Source & Chr(13) & “Error Line: ” & Erl & Chr(13) & Err.Description
Debug.Print Msg, , “Error”, Err.HelpFile, Err.HelpContext
End If
Resume Here

End Sub
Reply
tdalon

September 2017 at 5:42 pm

Hi. Nice post. I am looking for a dropdown list where I can (really) multi-select. e.g. holding the Ctrl or Shift key. Here with your solution I have to select each one-by-one. Ctrl-A to select all would be also nice.
Reply
Deborah Ridley

September 2017 at 3:34 pm

I have a workbook that needs different dropdowns in all columns and down 10-20 rows. Underlying data is on a separate sheet. Which sheet should the code be posted and how do I change the code to accomodate 20 different drop down lists
Reply
Devender Singh

September 2017 at 2:20 pm

Hi VBA code is not working in my excel sheet

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.Address = “$C$2” 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

what wrong with my excel
Reply
Sujai Veeramachaneni

September 2017 at 6:17 pm

Hi Sumit,

This is a great code and for the most part it’s worked well for me. I’m trying to apply this to a range of cells from B3:H1012. Can you please advise what part of the code needs to be changed? I would also appreciate if you can let me know the code it needs to be changed with. Thanks a lot in advance!!!
Reply
Liz

September 2017 at 2:20 am

Hello, this code works great. Thank you for sharing this great work. Unfortunately, I’ve come across an issue not yet addressed here. I need to protect the worksheet, but once I do that the code no longer works. Is there a solution for this? Thank you!
Reply

Leave a Comment Cancel reply