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

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:

select multiple items from a drop down list in excel - Multiple Selections

There is no way you can do this with Excel in-built features.

The only way is to use a VBA code, which runs whenever you make a selection and adds the selected value to the existing value.

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

 

This has been one of the most popular Excel tutorials on this site. Since I get a lot similar questions, I have decided to create an FAQ section at the end of this tutorial.

How to make Multiple Selections in a Drop Down List

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

There are two parts to creating a drop-down list that allows multiple selections:

  • Creating the drop-down list.
  • Adding the VBA code to the back-end.

Creating the Drop Down List in Excel

Here are the steps to create a drop-down list in Excel:

  1. Select the cell or range of cells where you want the drop-down list to appear (C2 in this example).Data for which you want to create the drop down
  2. Go to Data –> Data Tools –> Data Validation.make multiple selections in a drop-down list in excel - Data Validation
  3. In the Data Validation dialogue box, within the settings tab, select ‘List’ as Validation Criteria.select multiple items from a drop down list in excel - List
  4. In Source field, select the cells which have the items that you want in the drop down.selecting multiple items from an Excel drop down list - Source Data
  5. Click OK.

Now, cell C2 has a drop-down list which shows the items names in A2:A6.

As of now, we have a drop-down list where you can select one item at a time (as shown below).

Drop Down in C2 allows single selections

To enable this drop-down to allow us to make multiple selections, we need to add the VBA code in the back end.

The next two sections of this tutorial will give you the VBA code to allow multiple selections in the drop-down list (with and without repetition).

VBA Code to allow Multiple Selections in a Drop-down List (with repetition)

Below is the Excel VBA code that will enable us to select more than one item from the drop-down list (allowing repetitions in selection):

Private Sub Worksheet_Change(ByVal Target As Range)

'Code by Sumit Bansal from https://trumpexcel.com
' To make mutliple selections in a Drop Down List in Excel

Dim Oldvalue As String
Dim Newvalue As String

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
            Target.Value = Oldvalue & ", " & Newvalue
        End If
    End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

Now you need to place this code in a module in VB Editor (as shown below in the ‘Where to put the VBA code’ section’).

When you have placed this code in the backend (covered later in this tutorial), it will allow you make multiple selections in the drop down (as shown below).

Note that if you select an item more than once, it will be entered again (repetition is allowed).

select multiple items from a drop down list in excel - Multiple Selections

Try it Yourself.. Download the Example FileDownload File

VBA Code to allow Multiple Selections in a Drop-down List (without repetition)

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

Here is the code that will make sure an item can only be selected once so that there are no repetitions:

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

Now you need to place this code in a module in VB Editor (as shown in the next section of this tutorial).

This code will allow you to select multiple items from the drop down list. However, you will only be able to select an item only once. If you try and select it again, nothing would happen (as shown below).

Select Multiple Items from a Drop Down List in Excel-no repetition

Try it Yourself.. Download the Example FileDownload File

Where to Put the VBA Code

Before you start using this code in excel, you need to put it in the back-end, such that it gets fired whenever there is any change in the drop-down selection.

Follow the below steps to put the VBA code in the backend of Excel:

  1. Go to the Developer Tab and click on Visual Basic (you can also use the keyboard shortcut – Alt + F11). This will open the Visual Basic Editor.Selecting Visual basic in the Developer Tab
  2. There should be a Project Explorer pane at the left (if it is not there, use Control + R to make it visible). select multiple items from a drop down list in excel - Project Explorer
  3. Double click on Worksheet Name (in the left pane) where the drop-down list resides. This opens the code window for that worksheet.select multiple items from a drop down list in excel - Code Window
  4. In the code window, copy and paste the above code.Paste Code to allow multiple selections in drop downs in Excel
  5. Close the VB Editor.

Now when you go back to the drop-down and make selections, it will allow you to make multiple selections (as shown below):

Resulting drop down in which you can choose more than one item

Try it Yourself.. Download the Example FileDownload File

Note: Since we are using a VBA code to get this done, you need to save the workbook with a .xls or .xlsm extension.

Frequently Asked Questions (FAQs)

I have created this section 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 this list of queries first.

Q: In the VBA code, the functionality is for cell C2 only. How do I get it for other cells?

Ans: To get this multiple selection drop-down in other cells, you need to modify the VBA code in the backend. Suppose you want to get this for C2, C3, and C4, you need to replace the following line in the code:

If Target.Address = "$C$2" Then

with this line:

If Target.Address = "$C$2" Or Target.Address = "$C$3" Or Target.Address = "$C$4" Then
Q: I need to create multiple drop-downs in entire column 'C'. How do I get this for all the cells in the columns with multi-select functionality?

Ans: To enable multiple selections in drop-downs in an entire column, replace the following line in the code:

If Target.Address = "$C$2" Then

with this line:

If Target.Column = 3 Then

On similar lines, if you want this functionality in column C and D, use the below line:

If Target.Column = 3 or Target.Column = 4 Then
Q: I need to create multiple drop-downs in a row. How can I do this?

Ans: If you need to create drop-down lists with multiple selections in a row (let's say the second row), you need to replace the below line of code:

If Target.Address = "$C$2" Then

with this line:

If Target.Row = 2  Then

Similarly, if you want this to work for multiple rows (let's say second and third row), use the below line of code instead:

If Target.Row = 2  or Target.Row = 3 Then
Q: As of now, the multiple selections are separated by a comma. How can I change this to separate these with space (or any other separator).

Ans: To separate these with a separator other than a comma, you need to replace the following line of VBA code:

Target.Value = Oldvalue & ", " & Newvalue

with this line of VBA code:

Target.Value = Oldvalue & " " & Newvalue

Similarly, if you want to change comma with other character, such as |, you can use the following line of code:

Target.Value = Oldvalue & "| " & Newvalue
Q: Can I get each selection in a separate line in the same cell?

Ans: Yes you can. To get this, you need to replace the below line of VBA code:

Target.Value = Oldvalue & ", " & Newvalue

with this line of code:

Target.Value = Oldvalue & vbNewLine & Newvalue

vbNewLine inserts a new line in the same cell. So whenever you make a selection from the drop-down, it will be inserted in a new line.
Q: Can I make the multiple selection functionality work in a protected sheet? 

Ans: Yes you can. 

To get this done, you need to do two things:

Add the following line in the code (right after the DIM statement): 

Me.Protect UserInterfaceOnly:=True

Second, you need to make sure the cells - that have the drop-down with multiple selection functionality - are not locked when you protect the entire sheet.

Here is a tutorial on how to do this: Lock Cells in Excel 

You May Also Like the Following Excel Tutorials:

  • Patty says:

    How can i make multiple selection dropdownlist work in all columns of a worbook

  • RAMADAN says:

    Thanks for this.

    how to make the list visible while selecting more than one item from the list. now if i want to select 3 items from my list, i have to click 3 times and select each one separately. would more convenient if the list stays open until i finish selecting my items.

  • Benny says:

    Thank you, this is helpful! In my list, I have a wildcard (Other: ***), that I want people to be able to replace with an additional response. However, when I do that with the above code, it copies what I have listed and then shows it again with my changes.

    i.e.: First choose “Option 1”, then chose “Option: ***” which I change to “Option 4”. The output is “Option 1,Option ***,Option 1, Option 4”. I want to get rid of “Option ***” and “Option 4”.

    Any suggestions?

  • brett watkin says:

    Your code has nearly got me to what i need done , Many thanks. I just need to be able to select more than one on a drop down list on spacific cells, Currently any drop down box in colum c but ideally need to just choice for eg c13, c16 ,e16 can this be done

  • brett watkin says:

    I need to add multiple selection in just spacific cells can this be done

  • Amir Manzoor says:

    I need to create multiple drop-downs in multiple columns (like C and F). How do I get this for all the cells in these columns with multi-select functionality?

  • zulfiqar ali says:

    dear sir,
    very informative. can you please tell me how to apply multiple selections in multiple column in a protected sheet?

  • John says:

    I have Excel 2010 and cant seem to get this to work. Any ideas?

  • Mike says:

    Amazing! Thank you so much for sharing your expertise! I had never used any code of any kind in Excel and your step-by-step tutorial made this simple to execute.

  • Brandon Ch says:

    Is there any way to modify the code so that I can manually drag a cell’s contents into another part of the table? In the table I have built, each user has one entry per row, but they should be allowed to drag that cell to another column in the same row. As the code stands, the only way to do this is to delete the cell’s contents and retype it in the new column, which is not ideal.

    Thanks!

  • Lucca Avila says:

    I really need to know if there is a code that allows to remove the selected object in the multiple selection list. Please, it will be really helpful.

  • Dee says:

    Do you know if Excel have the function to have fillable group populate after you select from a selection list?

  • Vish says:

    Thanks Sumit for this excellent trick. This worked like a charm. Can you please advise, how can I now count the number of selections made?

  • Stuart says:

    I just finished using your code for reoccurring choices. Thanks, it works great. My next task is to work on exporting it to an Access DB.

  • Melissa says:

    First, Thank you. This is extremely helpful and it worked! I just have one issue. It changed my date format. My list has a date format of mmm-yyyy. When I add this VBA code it allowed multiple selection, but changed the date format from DEC-2018 to 12/01/2018. Is there someplace in this code I can specify the date format mmm-yyyy??
    Thank you in advance! Greatly appreciative of the help.

  • Akash jagtap says:

    How can unselect item from selected?

  • Tania says:

    I have copied your VBA without repetition but it doesn’t seem to work. Note I made two changes – (1) replaced the target address to $B$9 and (2) the spacing between selections from “,” to vbNewLine.
    Can you please give me ideas why this doesn not seem to work?

  • Suri says:

    It worked like a charm, instruction and layout is very clean and clear. thank you so much!

  • Belinda says:

    Hi I have just built my spreadsheet which is perfect. Now I need to make one of my drop downs editable. When you put other and want to put in an explanation.

  • Jean says:

    I’d like to alpha sort the multi pick list results so that the list displays alphabetically, not in the order it was selected. Any suggestions?

  • Jenny says:

    Hi, Thanks for this helpful guide. I copied the VB script and changed the column from C to P and it worked – however I needed to create multiple drop-downs in the entire column ‘P’ rather than C – when I followed the instructions given for this is only worked on column C – and I could not see what I needed to change to make it point to column P. Please can you advise what I need to change in the code to make this work. Many thanks,
    Jenny

    • Jenny says:

      So I figured this out now – I just needed to replace 3 with 16. However, I can see that there is a problem trying to deselect an item. I see other queries about this – is there a way to do that please.

  • Nahla says:

    Dear Sumit, thanks for the great code,it works fine with me however when i send the excel file to my college they couldn’t open the file they had the following warning ( this message had contained attached files that deleted during attachment filtering .xlsm) do you have an answer that could help me please

  • Deep says:

    How can we delete only one selected value form multiple Drop down option

    • Hyperion135 says:

      Try this:

      If InStr(1, Oldvalue, Newvalue) = 0 Then ‘ Add
      Target.Value = Oldvalue & vbNewLine & Newvalue
      Else ‘ Remove
      If InStr(2, Oldvalue, Newvalue) = 0 Then ‘ 1st entry
      If Oldvalue = Newvalue Then
      Target.Value = “”
      Else
      Target.Value = Replace(Oldvalue, Newvalue & vbNewLine, “”)
      End If
      Else ‘ Not 1st entry
      Target.Value = Replace(Oldvalue, vbNewLine & Newvalue, “”)
      End If
      End If

      • OscarMD says:

        Thx Hyperion135:
        had to make a few changes to your code.
        Here is what your final code looks like inserted in the original “without repetition code” provided by Sumit Bansal.
        Works like a charm!
        ‘ To allow multiple selections in a Drop Down List in Excel (without repetition & delete selected value from dropdown list function)
        Dim Oldvalue As String
        Dim Newvalue As String
        Application.EnableEvents = True
        On Error GoTo Exitsub
        If Target.Column = 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:
        If InStr(1, Oldvalue, Newvalue) = 0 Then
        Target.Value = Oldvalue & vbNewLine & Newvalue
        Else:
        If Oldvalue = Newvalue Then
        Target.Value = “”
        Else:
        Target.Value = Replace(Oldvalue, Newvalue & vbNewLine, “”)
        End If
        End If
        End If
        End If
        End If
        Application.EnableEvents = True
        Exitsub:
        Application.EnableEvents = True
        End Sub

  • Dipti says:

    I need to use Multiple select drop down for Marge Column Example :–
    I Have Marge C2 , B2 , D2
    What address do i need to provide

  • Hollie says:

    I have managed to get each selection to be on a separate line within the same cell, however when using that cell to place on another sheet within the same workbook the two are pushed together again. Is it possible to adjust this?

  • Lucy says:

    How do you deselect an item? I used the code without repetition, but now I want to deselect and item, but nothing happens and I can not delete the item. For example I select one, two from drop down list. Now I do not want two, how do I get rid of it? Thank you.

  • Gary says:

    Hi there, this doesn’t work when I protect the worksheet. I am using this code to make a rudimentary form, so I want to protect the other cells from editing. The cells that contain the dropdown are not protected but the vba still doesn’t work. As soon as I protect the worksheet the drop downs only allow a single selection. What can I do to make this work. My apologies if this is answered somewhere and I didn’t see it.

    Thanks for the great site and sharing your Knowledge!

  • Hayden says:

    This works great. I am having a problem counting the selections now. I have a separate count section that every time I select “white” in a column, it will then count those occurrences in another section. Now when I select “white, Black, native” the count doesn’t work. How do I fix this issue? Basically, I want to be able to select multiple options in a drop down and have them be counted separately. The code I use for the count is =COUNTIF(C:C, “BLACK,”)

  • KS says:

    This worked and was so easy! Thank you so much!

  • Trena says:

    I’ve managed to do everything but my multiple choices still kicks out the previous choice. I have gone to address and changed it to column and on $c$2 to 4 as I want the whole column.
    I’m doing this for a non for profit service dog trainers so we r not techies. I thought I was following the directions is my software or system too old? It really need this little thing it could change our who training program. Thank you in advance I feel you were very easy to follow but I am missing something.

  • JMB says:

    Hi Sumit – your VBA instructions are great and I love that you’ve thought about all the other uses (ie duplicates / non duplicates, separate line in cell) as it’s perfect for what I want! I’ve done some tests which all work fine and will add the code to my working spreadsheet. Fingers crossed!

  • Pearlyn says:

    I noticed that after adding the codes that allows me to select item once, I can’t delete the last input if it was incorrectly selected. It will give me validation error message. How do I counter this issue?

  • SG says:

    can I applied this for other columns in the same worksheet?

  • BMRHON says:

    You are brilliant! thank you very much for posting this. It seems to work great!!

  • Karina says:

    Thank you for this code, works great. How do you deselect an item? I used the code that does not allow selection of the same item but now I want to deselect but nothing happens and i can not delete the item. For example I select one, two from drop down list. Now i do not want two, how do i get rid of it. Thank you.

    • sarah says:

      I also want to know this

  • Jared says:

    How do I alter this code so that there is an ending row for the multiple selections of the drop-down list?

    For example, I have a table on excel with a drop-down list in columns 2 and 3 (B and C). I would like to alter this function so that at the bottom of this table, the function stops. I would also like to be able to use functions, such as count in column 4 which is D, on excel.

    All help is appreciated, thanks so much!

  • Tracy says:

    I cannot make it work. I downloaded the file to see what the problem was and I cannot make it work with that either. I can still only have one selection from my dropdown box. What am i doing wrong?

  • Brent says:

    Thanks for the tutorial but I think I must have missed something.

    I put information for drop down list in Sheet 2.
    Sheet 1 – created drop down list and targetted information in Sheet 2.

    Pressed Alt-F11 and got the Visual Basic for Applications (VBA) screen, selected Sheet 1, pasted the code and closed VBA.

    Tried to select more than one item from drop down list and only got one item showing at a time.

    Reopened VBA, deleted code from Sheet 1 and copied the code to Sheet 2 then closed VBA.

    Tried to select more than one item from drop down list and only got one item showing at a time.

    Not sure if need to have a particular cell or sheet selected before Alt F11?

    Using Microsoft Office 365 ProPlus.

    Cheers.

  • ken says:

    How can I get the code to choose items in a dependent dropdown list? Suppose I have a different list in each dropdown?

  • Nick says:

    Thank you. Hope this will help this old man learn and understand excel

  • Mase says:

    I copied and pasted the code and it works!!!

    now, how do you sumifs multiple values from multiple drop down list using named ranges ?
    i.e. suming multiple product groupings as well as multiple regions .

  • Mase says:

    I copied and pasted the code and it works!!!

    now, how do you sumifs multiple values from multiple drop down list using named ranges ?
    i.e. suming multiple product groupings as well as multiple regions .

  • Karen Behrns says:

    Hi, How can i get this to apply to multiple worksheets?

  • Mohammed Khaja Hussain says:

    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.

  • Olivia Kebble says:

    Hello,

    When making multiple selections, how do you then delete a selection you may have accidentally clicked?

    • superloid says:

      that is also what I was going to question. Only deleting entire value works currently.

  • Mahesh Babu says:

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

  • Lucas Vieira says:

    edited code so that the list removes a duplicate element if that happens: https://pastebin.com/N9RGFkBZ

  • Kielce Gussie says:

    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.

    • JMB says:

      Hi – you need to replace H with the number of the column which in this case is 8

  • Chris Caesar says:

    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.

  • Dan says:

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

  • Tanya Sawrie says:

    Is there a way to count the items later on if they are listed in a multiple list?

  • Madhur says:

    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?

  • Michael Babines says:

    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?

  • Justi says:

    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.

    • Sumit Bansal says:

      You will have to change the code so that only the cells where you want multiple selections are included in the code

  • Justi says:

    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

  • SmrutiS says:

    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?

    • Sumit Bansal says:

      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

      • SmrutiS says:

        thank you, it worked!

  • Dirk Dinnewet says:

    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

  • Rohit panwar says:

    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.

  • Govardhan says:

    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.

  • @lex says:

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

  • Poornima Iyengar says:

    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

  • Allison Zagiel says:

    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?

  • Kris Hamilton says:

    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?

  • Azim Boblai says:

    Hi Sumit

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

  • Alec Beckman says:

    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!

  • Kristi Matthews Ward says:

    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!

  • Alessandro Renzi says:

    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.

  • Vicky McCabe says:

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

  • Mana Fazel says:

    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

  • ELISABETH says:

    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 ?

  • TL says:

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

  • prem parth says:

    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?

  • Aviral Mittal says:

    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

  • Aviral Mittal says:

    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

  • aaron says:

    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

    • Aviral Mittal says:

      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.

  • mahzuz tom says:

    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?

  • Meer says:

    Hi, is there any way to add scrolling feature to the drop down?

  • Peter Folmer Hansen says:

    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?

  • JP says:

    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.

  • Heather Botefuhr says:

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

  • dolson57 says:

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

  • Laura says:

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

  • Razaul Karim says:

    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

  • Marcia Cooper Gschwind says:

    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.

  • Allen Ibsen says:

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

  • Karen Curtis says:

    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?

  • Mary says:

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

  • Mike Knerr says:

    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

  • tdalon says:

    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.

  • Deborah Ridley says:

    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

  • Devender Singh says:

    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

  • Sujai Veeramachaneni says:

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

  • Liz says:

    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!

  • Jasmine says:

    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

  • Ben Muir says:

    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

    • Sumit Bansal says:

      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.

  • Beau Ties Ltd of Vermont says:

    This was working great and it just stopped working suddenly. Not sure why.

    • Beau Ties Ltd of Vermont says:

      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.

  • Manisha Mehra says:

    Can we put multiple VBAs in the same sheet for different cells having separate dropdown lists.. I tried it does not work.

    • Sumit Bansal says:

      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.

  • Dorsey says:

    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.

    • Sumit Bansal says:

      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

  • Sue Retzer says:

    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?

  • Momo says:

    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!

  • Katie Axtell says:

    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!

  • Silroy says:

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

  • Shaniko Nichol-Driskill says:

    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.

  • Shaniko Nichol-Driskill says:

    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

  • JW says:

    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.

  • Jim Baines says:

    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.

  • Evan Albrighton says:

    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

    • momo says:

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

  • Richard Abell says:

    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!

  • nat says:

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

  • Triuwida says:

    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?

  • Brittany Argotsinger says:

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

  • Brittany Argotsinger says:

    What if my dropdown lists are in a different sheet than the dropdown data?

  • T.L. says:

    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

    • T.L. says:

      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

  • Lisa Wrona says:

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

    • Brittany Argotsinger says:

      I am having the same issue. Do I need to modify any of the code for my dropdown cells?

      • Brittany Argotsinger says:

        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!

  • Ashley Curiel says:

    @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?

  • Maria Hooker says:

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

  • farid abaoui urbano says:

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

  • karthi keyan says:

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

  • Andrew Starling says:

    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.

  • pernillepar says:

    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!

  • Shawn Cook says:

    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?

    • Shawn Cook says:

      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

  • Viv says:

    Is there a way to deselect something from the list if it was clicked by accident?

    • Viv says:

      Never mind. Was able to look for a previous comment below

  • tallnewenglander says:

    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?

    • momo says:

      let me know if you ever found a solution!

  • Linh Cao Ngoc Nguyen says:

    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.

  • karthi keyan says:

    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.

    • Sumit Bansal says:

      Hey Karthi.. I don’t think it’s possible to apply any kind of formatting in the drop down.

  • Erin Bresnahan says:

    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

    • Sumit Bansal says:

      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?

  • Daniel Lee says:

    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?

  • Mary Dronca says:

    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

  • Philipp Krohn says:

    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!

  • Ryan O'Keefe says:

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

  • COURTNEY says:

    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.

  • Bikke Chettri says:

    I saved on format as reccomeneded. But when I reopen it it stops working. Any one know why ?

    • momo says:

      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.

  • Duxqdj says:

    @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

  • vijay raj says:

    Hi Sumit, is there a way for to vlookup code for the multiple drop down list selection for the above code

  • M.A. Rohani says:

    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

  • Dana Bigler says:

    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.

    • Mary says:

      I pose same question; did you get an answer

  • Nel Mag says:

    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.

  • Babobim Thip-utai says:

    Wonderful! Thank you!

  • Andrei Caraus says:

    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 !

    • Sumit Bansal says:

      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

      • Andrei Caraus says:

        Thanks for the quick reply !
        Using XLS or XLSM format does not work for me but i succeeded using XLSXM format.
        Thank a lot !

  • Anna says:

    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.

    • Sumit Bansal says:

      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.

      • Anna says:

        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!

      • Brittany Argotsinger says:

        What do I use for a range of cells in place of $C$2?

  • Heather Reisig says:

    Dear Sumit,
    You are THE man. Thank you.
    That is all.

  • Lara Mackenzie says:

    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.

    • Sumit Bansal says:

      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)

      • Lara Mackenzie says:

        You are a star…. thank you. Your help is amazing.

      • Lara Mackenzie says:

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

        • David Hilyard says:

          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.

      • David Hilyard says:

        Sumit – this is brilliant! It has really helped me. Thank you.

      • Michelle says:

        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!

  • TJ says:

    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.

    • Sumit Bansal says:

      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

  • Nished prome says:

    Hi Sumit . Thanks a lot for this great video . In drop down tap beside the list there will be a option that will allow me to insert whatever i want to insert .Is it possible ? Please help me

    • Sumit Bansal says:

      Go to the Data tab and click on Data Validaiton. In the data validation dialog box, in the Error Alert tab, change the from Stop to Information. Now you will be able to make the changes and enter manually in the cell.

  • David McDowell says:

    This has been really helpful, thank you so much. Is it possible to make this VBA code work when a sheet is protected?

  • Raja Sekhar says:

    For Protected Worksheet:

    Option Explicit

    Private Sub Worksheet_Change(ByVal Target As Range)

    Dim wsh As Variant
    For Each wsh In Worksheets(Array(“Sheet1″))
    wsh.EnableOutlining = True
    wsh.Protect UserInterfaceOnly:=True, Password:=””, _
    DrawingObjects:=False, _
    Contents:=True, _
    Scenarios:=True, _
    AllowFormattingCells:=False, _
    AllowFormattingColumns:=False, _
    AllowFormattingRows:=False, _
    AllowInsertingColumns:=False, _
    AllowInsertingRows:=False, _
    AllowInsertingHyperlinks:=False, _
    AllowDeletingColumns:=False, _
    AllowDeletingRows:=False, _
    AllowSorting:=False, _
    AllowFiltering:=False, _
    AllowUsingPivotTables:=False
    Next wsh

    ‘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 = “$C$2” Then ‘As required
    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

    Exitsub:
    Application.EnableEvents = True

    End Sub

  • Elisabeth says:

    This is a great bit of code and it’s working well for me. I have a question about whether or not I can use the code if the table I’m working in has been formatted as a table. Instead of “3” in the following line:

    If Target.column = 3 Then

    is it possible to use a reference to the table and column? Table1[column1]?

  • Amy Martin says:

    Hello, I followed the instructions and it worked perfectly. Is it possible to edit the above code so that when you click multiple selections it forms a list down multiple cells in a column, rather than in one cell separated by commas?

  • Ryan Kelly says:

    I have followed this to the letter and still only get one entry at a time. I have set up drop downs in every cell under column’s B,E,F. I want B to remain a single selection, but I want to have the option for E and F to have multiple entries (items) Any help would be appreciated

  • Balint Ciprian says:

    Thank you for your great work! If I need to select some Items and then add those in the cell, how can I make this?

    • momo says:

      hi did you ever figure this out??

  • sukhi says:

    Hello, How to I create multiple selections as a list in a new column rather than getting values separated by coma in same cell?

  • Annie Zhou says:

    Hi Sumit, thanks for the codes. The multi-select list worked great but I have issue with the existing single-select list. Before I added your code, validation for the single-select list worked. I only could select the value from the list. But after I added your codes, the validation for single-select list didn’t work. I was able to enter any values to the single-select list and I didn’t get an error message. If I only select the value from the list then it’s fine. But I still could enter any value and the cell will accept it.

    Do you know how to fix this? Thanks so much for your help!

  • Priya Wagh says:

    Hi Sumit,

    If I have to delete an entry from the list, it does not behave the way it should. Have you tried that?

  • Pepijn Olivier says:

    awesome post, got it workin in under 5 minutes. Exactly what I wanted, thanks, keep up the good work

  • Heidi Van Wheeler says:

    LIFE SAVER!!!! So here’s the tricky part. How do I now filter my column with multiple items so that everything with “A” appears…even though it has other names along with it “A,B”, “B,A”, “C,A”

    • Michelle says:

      I would like to know a solution for this also.

  • Muns says:

    Great macro thanks! TRUMP RULES!

  • Mukul Apte says:

    Hi Sumit, is there a modification to the code if I want the next selection from the dropdown in the next line. Like when we the ALT+Enter function : for eg
    one
    two
    three
    Instead of :
    one, two, three

  • Mukul Apte says:

    What is the solution to pepperleafev’s problem of duplicating values? I am facing the same problem after using the code

    • Robin Peskar says:

      I am having the same issue…any answers???

  • Chris Milton says:

    Hello Sumit, thank you for supplying such helpful information, I have used the code and works well, not sure if this has been covered yet, but is there a way for the selected data to display down the column instead of in the one cell?

  • spyrule says:

    I came across your code chunk, and for the most part it works well.

    A minor issue found:

    FROM:
    If Oldvalue = “” Then
    Target.Value = Newvalue
    Else
    Target.Value = Oldvalue & “, ” & Newvalue
    End If

    TO:

    If Oldvalue = “” Then
    Target.Value = Newvalue

    ElseIf Target.Value = Oldvalue Then ‘= 2 And Target.Row <

    • Sumit Bansal says:

      Thanks for sharing! Makes sense

      • Pepperleafev says:

        I think I’m having the problem of duplicating values. If I make multiple selections from a drop down list for a cell and then try to put in something that is not from the drop down list, I end up with several repetitions of what I had selected in the same cell. Then when I try to delete some of the repeated values, it will repeat itself again in the same cell. So it becomes an endless cycle of repetitions until I just delete the entire cell. But I can’t figure out how to fix this. I tried to put in the code that spyrule shared but I may not be doing it right. I’m new to Vbasic so I don’t really understand the code…

        This is what I ended up with:

        Oldvalue = Target.Value
        If Oldvalue = “” Then
        Target.Value = Newvalue
        Else
        If Target.Value = Oldvalue Then
        GoTo Exitsub
        Else
        Target.Value = Oldvalue & “;” & Newvalue
        End If
        End If
        End If
        Application.EnableEvents = True

        Thank you for posting. This was really helpful.

        • Mukul Apte says:

          Hi Sumit, id you give an solution to this problem. I cannot seem to find a solution here.

  • Ramon says:

    Hi thank you for this nice tutorial. Now, instead of separating the multiple selections by a comma, I want to add the additional selections in the adjacent cells in the same row. But still have the feature of removing previously selected items. Can you please help me with that?

  • Ramon says:

    How does the code need to be changed if, instead of separating the multiple selection by a comma, the additional selections are added in separate cells in the same row? But still with the replacing feature included?
    thank you very much for your help!!

  • Dara says:

    Hi, I’ve been looking for this. This is a very great tip. Thank you so much for this advise.
    Wish you the best,

    Dara from Cambodia

  • Paul Cas says:

    The code worked great and I was so happy with finally being able to add my products within the same cell.
    The next day however I went to open the file and now the code isn’t working??? It just went back to normal> I saved it as a Macro as well.
    Does anyone know what I can do to fix this issue???
    Please help and Thanks in advance!

  • Mikkel Henriksen says:

    What if I wish to have more than one dropdown with different multible choice

  • webo says:

    Hi Sumit

    a great thanks for great efforts
    I have zero VBA knowledge, so used your code to work with
    I already saved as XLSM, however
    every time I enter a value in droplist, then try to select another value from it, I get an error “syntax error”
    something strange, though I ready your code worked smoothly with other readers, only sadly with me, didn’t
    I hope you can reply to me with solution or cause of error at least

    thanks
    webo https://uploads.disquscdn.com/images/6b616b8c296f18f73245dd1ceba825bad42f3a7e5982c0ec1d7b9ddfd4275874.jpg

  • kevin patel says:

    Hi Sumit. I have read through all of the posts and it has helped me a lot. Just one more question if you don’t mind. I need a secondary list to select the items from that would only display the items that I selected in the first list. Please help me out as I am working for a company and this database needs this function immediately for me to start entering the data in it accordingly.
    Thank You for your post and your help.

  • Smilin' Joe Fission says:

    Hi Sumit,

    Thank you for sharing your code. I am able to get it to work except that when I make a 2nd selection (or 3rd, 4th, etc.), I get a green triangle trace error in the cell. It is saying that the value doesn’t match the data validation restrictions defined for the cell. Do you know what may be causing this and how to fix it?

    Thanks

    • Smilin' Joe Fission says:

      I’ve been playing around with this some more and have realized that the error only appears when I have my spreadsheet formatted as a table. I’m not sure why, but when it is not formatted, there is no error. Is there any way to fix this or change the code to address this?

      • Sumit Bansal says:

        Hey.. I tried converting the data into a table and see if I could replicate the error. It worked fine for me. Would be great if you could share your file. Can have a look and see what’s causing that.

        • Smilin' Joe Fission says:

          Thanks, Sumit. I tried creating this in new worksheets as well, having the same problem. I would create the drop down list and it would work fine. But as soon as I “format as table” the trace error appears.

          How can I send you the file?

  • Nicole says:

    Hi Sumit, Thanks for the code, but after I close it, I cannot run it. Usually I assign the maro to a shape box, but since it is a drop down menu, I couldnt assign a macro name and it is not running. Wondering why? Thanks!

  • Ellen M says:

    Hi there,

    I’m struggling a bit with what looks like others have been able to solve below. I have a spreadsheet where I’d like to enable multiple pick lists in columns E, M, and N only (down to row 100 or so in each). I don’t want multiple pick list in the other columns. Can you tell me exactly what to enter for the code? I’d be most grateful for your guidance.

  • Dutch says:

    Hi, when i attempt to filter a column that has cells with more than one value- the filtering system cannot pick out individual values, and instead picks them all per cell. Is there anyway to filter based on ONE value for all the cells(those that have many values and those that have one-separated by a comma?

  • Priyanka says:

    Thanks. This was very useful. Precisely what I needed.

  • Fermeture says:

    Hi, Can somebody help me change target is one address to one Range ==>
    If Target.Address = “$C$2” Then

    Thanks

  • Shelley Hughes says:

    This is great! Thank you so much for this. I have one question though. I’ve read the comments and can’t find the same question being asked – apologize if I missed it.

    I am able to select multiple options from a dropdown box successfully, however I can’t find a way to them remove one of the options unless I clear the entire cell.

    For example, I select options such that my cell looks like: Apple, Orange, Banana.

    I no longer want Orange selected. If i try to delete the Orange text, it doesn’t work.

    The only way I’ve found to do this is to delete all contents of the cell, then go back and select Apple and Banana from the dropdown.

    • Sumit Bansal says:

      Hello Shelley.. This is the drawback of using a drop-down list here. As soon as you delete an item and hit enter, Excel takes it as another entry that you are trying to make, and shows an error since that’s not a part of the drop down.

    • Sumit Bansal says:

      Hello Shelley.. This is the drawback of using a drop-down list here. As soon as you delete an item and hit enter, Excel takes it as another entry that you are trying to make, and shows an error since that’s not a part of the drop down.

      • John Ferry says:

        Hi, this code seems to be working well for me, but can you tell me if it’s possible to ensure that once the selections are made they appear in alphabetical order?

        • Duxqdj says:

          Hi, am also facing this problem can you resolve this one?…

  • Angie says:

    Hello I add the code to be used in C8 for 2 spreadsheets, the drop meny works only in C8 but I want it to be working until C200, please advise.

    Angie

    • Sumit Bansal says:

      Hello Angie.. you can replace this line: If Target.Address = “$C$2” Then
      with this line:
      If Target.column = 3 Then

      Now the drop down will work for all the cells in Column C

  • Ange says:

    Hi there – I tried to use this code but it is not allowing me to add multiple values in 3 of my columns.

    Option Explicit

    Private Sub Worksheet_Change(ByVal Target As Range)

    ‘Code by Sumit Bansal from http://www.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 = “$J$2” Or Target.Address = “$K$2” Or Target.Address = “$L$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
    Target.Value = Oldvalue & “, ” & Newvalue
    End If
    End If
    End If
    Application.EnableEvents = True

    Exitsub:
    Application.EnableEvents = True

    End Sub

    • Ange says:

      Actually – it is working in all columns but only in cell 2 on each.

      • aldrin says:

        Same issue???

        • Ange says:

          Yes same issue – actually I abandoned this option because I wanted to be able to filter on just one value within the whole field (set of suburbs for the South in one and wanted to filter on just one of those suburbs) but it won’t allow me to filter that way.

          • lee b says:

            hello Ange
            can you tell me what option you decided to use. thanks

  • Gina van der Klei says:

    Hi Sumit,
    This worked great thanks, this question leads on from what I have now achieved with this code. Now that I have selected multiple entries in some cell (I have applied this code to an entire column) but not all, I would like to filter down to entries within that column, ie find all entries that contain orange or blue. So I have applied the usual filter to my heading row but when I click on this I would like the options to filter to appear the same way it would if there were only one entry in each cell, but it has the lists/multiple entries as options. Basically I want filter function to comma separate my lists I guess? Does that make sense? Any suggestions? I can just type in the colour in the search option under filter but that doesn’t work if I want multiple colors at once.
    Thanks,

    • Dutch says:

      did you find a solution? i have been looking for days and there appears to be no way…

  • Jessica Puffenbarger says:

    I am having an issue with the code. My lists are in sheet 2, but my drop downs are in sheet 1. How do I need to change the code to accommodate this? Thank you!

  • frabk B. says:

    Great solution. How can I get each entry to go into an new line (issuing a line feed after the selection?

  • avi mor says:

    another thing how can I defind that with in a table in excel and that it could move automaticly with the table?
    thanx

  • avi mor says:

    hi, followed this thread found it the most helpfull, but I need to tweek it a little bit more.
    I need the selection to be words (strings) and after the selection the return value needs to be a sum of numbers, each word get its value – a number.
    how can I do this ?
    please help, I have been struggling with this one for three moths now.
    thanx.

  • Alex says:

    Thank you, this has been very helpful. I have set up multiple selection list in L5 using your code. In M5, I have a dependent selection list that recognizes a selection in L5. However, I am having trouble with this dependent list recognizing multiple selections. Any thoughts?

  • MarkWisdom says:

    This is a great solution for MS Excel, do you have any idea if something similar would work for MS Project?

    • Sumit Bansal says:

      Hello Mark.. I am not sure if this can be done with MS Project. I know a guy who is an MS Project champion. Will ask him and post back

  • T Ware says:

    How do I make the macro work on a range of cells? For example cells L2 through L10000.

    • Sumit Bansal says:

      You’ll need to modify the code. Change the following line:
      If Target.Address = “$C$2” OR Target.Address = “$D$2” Then
      to
      If Target.Column = 11 AND Target.Row > 1 AND Target.Row < 10001 Then

      • Cindy Bowen says:

        I was using
        If Target.Address = “$G$2” Then

        This worked on the one cell – allowing multiple selections in the same cell with a comma between.
        I need to allow this on the entire column. When I change the code to

        If Target.Column = 6 Then

        Or

        If Target.Column = 6 AND Target.Row > 1 AND Target.Row < 10001 Then I can not longer select multiple selections in the same cell. What am I doing wrong?

  • Khushal Roopnarain says:

    I need to know how to do TWO drop down lists with multiple choice selections on both . this must happen on the same sheet. Thanks

    • Sumit Bansal says:

      Hello Khushal.. You’ll need to modify the code. Change the following line:
      If Target.Address = “$C$2” OR Target.Address = “$D$2” Then

      Change the references to what you want.

      • Khushal Roopnarain says:

        Hi. Would this allow me to do two concurrent multiple choice selectons. I am assuming I need to have 2 target address in the VBA code.Forgive my zero sense of VBA. Thanks

        • Sumit Bansal says:

          Yes this should do it. The line in my last comment specifies two target address which would enable both the drop downs in the these cells to have multiple selection functionality

        • Khushal Roopnarain says:

          Hi. Just tried your suggestion and it works . Your’e great . Thanks

        • Khushal Roopnarain says:

          Hi Sumeet. I have tried your code suggestions and it works I have even tried 4 drop down list multiple selections and it works.

          Just one question, when I save my work I am askes to save as a macro sheet. Is this the only way to save. What is your sugestio on the safest way to save. I may want to share this with others as well and they must be able to open the file.

          • Sumit Bansal says:

            You’ll have to save it in either .XLS or .XLSM format. Since it contains a macro, you can’t save it in the .XLSX format. Once you save it, it won’t show the prompt again. You can also share it with other people and there shouldn’t be any issue.

          • CAB says:

            Help…saved as .XLSX format but when I save, close and reopen the coding is gone!

          • Glaucia Nicholson says:

            Yes, I have the same issue. I tried .XLS and .XLSM. Both happens the same… the code is gone after I reopen my file. 🙁

          • chris says:

            Hi Sumitji, I am wondering if there is a solution to this question. I “save as” and on the copy, the code is gone and I can no longer make multiple selections from the drop down list. I am creating a mental health treatment plan template so I would like to be able to use this template over and over again for new patients. Any ideas on how I can “save as” and retain the code and formatting?

  • Kirsten Balutan says:

    Hi Sumit. Is it possible to have another drop down list under a different column? If yes, how do I do it? Thank you!

    • Sumit Bansal says:

      Hello Kirsten.. You can have the same functionality for any cell/column. You would need to change this line in the code:
      If Target.Address = “$C$2” Then

      If you want it for an entire column (say column D), make it:
      If Target.Column = 4 Then

  • CVG says:

    This is really great, I’ve been looking for this option. My question is, I’ve followed your instructions from above to modify the code and get it working on just one column, in this case column 7.

    However, I’d like to get it working on column 6 too but instead of having the comma seperate each value I want to use a hyphen instead. So currently on column 7 the output is “1, 2, 3, 4”. On column 6 I want the output to be “1-2-3-4”.

    I’ve played around with the code a bit but I can’t seem to get it right…any suggestions?

  • Emily K says:

    Can you use this concept and remove options as they are chosen. The additional tutorial above Creating Multiple Drop-down Lists in Excel without Repetition removes but its multiple cells. I want something that allows multiple selections as above tutorial but removes them as you choose them so they are not duplicated. TIA

    • Sumit Bansal says:

      Hello Emily, you can use the below code to make sure an option doesn’t get selected multiple times:

      Private Sub Worksheet_Change(ByVal Target As Range)
      ‘Code by Sumit Bansal from 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

      • Emily K says:

        Is this code in addition to the original code in the tutorial? You didn’t specify if this is a replacement or in addition to. TIA. Great tutorial by the way.

        • Tarak M Shah says:

          Hi i am also facing the same issue how can we run the code while having the sheet protected… pls help its really urgent

      • Tarak M Shah says:

        Hi how can we run the code while having the sheet protected… pls help its really urgent

  • akshaybarve says:

    Great post. Followed it but can you suggest what to do if we want to delete / remove a selection made earlier. I mean suppose we select three mutiple options and i have to remove second one then how to do it? Also how to apply this to entire column (i.e from c2 till end)

    • Sumit Bansal says:

      You can modify the code to automatically delete an entry when you select it again, but I believe it would be easier if you simply delete it manually (unless you have tens/hundreds of options selected. To apply this to all the cells in column C, replace the line

      If Target.Address = “$C$2” Then

      with

      If Target.Column = 3 Then

      • Doug says:

        Hi Sumit, I have run into a snag. I started with your code at the top of the thread and started making changes based on your recommendations.
        1. Allow for multiple selections. (First code given on page) -Works.
        2. changed the code from Target.address = “$C$2 Then to If Target.Column = 10 And Target.Row > 3 And Target.Row < 43 – Works.
        3. I changed the code per your recommendation so you can't select the same option again. – Works.
        I ran into a snag when I need to un-select a previously selected item from the list. I tried to delete the text in the cell, but it gives me the error box. (Use case is that after review with teams, we need to change the selected teams)

        Second Question, From a user experience perspective do you have a way to do this with Checkboxes so you can select all at once (either selecting or deselecting) the radio buttons for each item?

        Thanks for all your help!
        Doug

      • Manish says:

        Hi Sumit!
        Thanks in advance for providing us the code for multiple selection in drop down list. I am facing the problem in deleting. As soon as I delete any wrong selection from the list and hit the enter or tab key, it re-appears on the same list. Please help

        • Priya Kumari says:

          Hi,
          Were you able to find any solution for deleting/removing previously selected items?

      • Soumya says:

        Hi Sumeet,
        Thanks for the code! Can you please let me know how can i deselect an entry?I am not able to delete an entry manually.
        Thanks in advance.
        Regards,
        Soumya

  • Anu says:

    this is really cool! thanks a lot! Can I have combine with the drop down ists in Excel without Repetition?

    • Sumit Bansal says:

      Hello Anu.. Below is the code you can use. If there is already a value in the cell and you select it again, it will not append it to the existing value.

      Private Sub Worksheet_Change(ByVal Target As Range)
      ‘Code by Sumit Bansal from http://www.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

  • Arielle Anderson says:

    I am working with 2010 Excel. I am right clicking on the sheet name to bring me to the “View Code” option. However, once I insert your code into the box, nothing is happening. What could I be doing incorrectly? Aside from trying code when I have no business doing that…=)

    • Sumit Bansal says:

      Hello Arielle.. I have hard coded the cell C2 in the code. You will have to change that cell reference for it to work for you. For example, if you drop down is in cell D2, then change it to D2. Or if there are multiple drop downs in column D, then use Target.Column = 4

      • edwin says:

        hi sumit, how do I get the target.culumn = 4 to work for two different columns in the same sheet

      • edwin says:

        hi sumit, how do I get the target.culumn = 4 to work for two different columns in the same sheet

        • Sumit Bansal says:

          Hello Edwin. You can use OR in the code to apply it to multiple columns. For example, you can use Target.Column = 4 or Target.Column = 5

  • JohnS says:

    The code to allow multiple selections works great. Do you know how to allow editing of the cells after selections are made? I can’t seem to remove previously selected items. Thanks

    • Epps says:

      https://www.youtube.com/watch?v=cRpTzOnaf48

      Here is VBA code included with video for drop down list with multiple selection that also allows you to remove previously selected items by reselecting them.

      • Lara Mackenzie says:

        Could you share the code as this is just what I am looking for? Thanks

      • Palak says:

        Hi Sumit,
        Whenever I close the excel file and re-open the code disappears. I have to re-paste the code every time. Any solution?

        • Epps says:

          you have to enable macros when reopening. Is it saved as macro-enabeled workbook?

  • Jason H. Parker says:

    Hi, followed this successfully, thanks for the tips. That said, I’m unable to replicate using this code, even when using “Or” commands along the “$C$2” line, to have the code apply to more than one drop-down list within the main workbook. Can you advise me on how I can write/adjust the code such that I can have multiple drop down lists where I am able to select more than one option?

    • Jason H. Parker says:

      For clarification, I want to build a matrix/table where I can select multiple drop-down options across 3-4 columns and 25-40 rows. So, wondering how I will need to adjust the “$C$2” part of that code to include the code for all of the cells in which I’d want to do a multi-select. That make sense?

      • Sumit Bansal says:

        Hello Jason, If you want this to be applied to all the drop downs in your worksheet, remove the following line from the code:

        If Target.Address = “$C$2” Then

        Also remove one the END IF from the end of the code.

        • Claudia Pêgo says:

          Hello. This information was vital, thanks.
          But if I don’t want to apply the code to all the drop downs in the worksheet, only in on row? For example, only in g5:g53?
          Can you tell me how to do this?

          • Sumit Bansal says:

            Hello Claudia.. In the code, you can replace the line

            If Target.Address = “$C$2” Then

            with

            If Target.Column = 7 And Target.Row > 4 And Target.Row < 54 Then

          • Claudia Pêgo says:

            Hello Sumit; thank you so much, it was just what I needed.

          • Phil says:

            I want to apply the VBA Code to cells C7:C80. Is the following correct “If Target.Column = 3 And Target.Row > 6 And Target.Row < 81 Then"? Thanks!

          • Lexis says:

            Phil thank you so much this was just what I needed

          • Sara Parker says:

            Thank you Sumit! It worked for me today. Happy New Year 2018!

        • Emily K says:

          When I did this it worked for the drop downs but it also caused every cell to show multiple entries. Is there a way to apply it to a specific number of cells, say 5. Thanks in advance.

          • Sumit Bansal says:

            Hello Emily.. You can specify the cells in this line

            If Target.Address = “$C$2” Then

            For example, if you want the drop down to work on C2 and C3, use:

            If Target.Address = “$C$2” OR Target.Address = “$C$3” Then

          • Emily K says:

            Thanks so much for the assistance. Everything is working well now. I do have anther question. Is it possible to have an option for the user to add their own entry to a list? I know I can turn off the the error message and allow them to type something, but I want their to be an item on the list like “other” and then when they select it they can enter their info. How can I make this work? Any suggestions. I have spent an hour searching online without any results. TIA

          • Kim R. says:

            Hi Sumit – Thank you for this wealth of knowledge! I am trying to apply this code to 3 different drop down lists in the same worksheet – cells P7:P70; AD7:AD70 and AH7:AH70. How can I do that? Thank you!

          • Kim R. says:

            Never mind…figured it out! Thanks for the post/information though!

            Private Sub Worksheet_Change(ByVal Target As Range)

            Dim RngDV As Range
            Dim Oldvalue As String
            Dim Newvalue As String
            If Target.Count > 1 Then GoTo Exitsub

            On Error Resume Next
            Set RngDV = Cells.SpecialCells(xlCellTypeAllValidation)
            On Error GoTo Exitsub

            If RngDV Is Nothing Then GoTo Exitsub

            If Intersect(Target, RngDV) Is Nothing Then
            ‘do nothing

            Else
            Application.EnableEvents = False
            Newvalue = Target.Value
            Application.Undo
            Oldvalue = Target.Value
            Target.Value = Newvalue
            If Target.Column = 16 Then
            If Oldvalue = “” Then
            ‘do nothing
            Else
            If Newvalue = “” Then
            ‘do nothing
            Else
            Target.Value = Oldvalue _
            & “;” & Newvalue
            End If
            End If
            End If
            If Target.Column = 30 Then
            If Oldvalue = “” Then
            ‘do nothing
            Else
            If Newvalue = “” Then
            ‘do nothing
            Else
            Target.Value = Oldvalue _
            & “;” & Newvalue
            End If
            End If
            End If
            If Target.Column = 34 Then
            If Oldvalue = “” Then
            ‘do nothing
            Else
            If Newvalue = “” Then
            ‘do nothing
            Else
            Target.Value = Oldvalue _
            & “;” & Newvalue
            End If
            End If
            End If

            End If

            Exitsub:
            Application.EnableEvents = True

            End Sub

          • Maria Hooker says:

            Thanks, I needed this

          • Cath McNally says:

            I want to apply your code (allowing one instance of multiple choices from a drop down) but it applies this code to the whole sheet so cells that are just to be typed into can have multiple entries, so if someone types into a cell then goes back and wants to over write this info it appears side by side in the one cell with a , seperating them,
            I only want to apply your code to cells C12 to C16 and have made the follwoing adjustments:

            Private Sub Worksheet_Change(ByVal Target As Range)

            Dim Oldvalue As String
            Dim Newvalue As String
            Application.EnableEvents = True
            On Error GoTo Exitsub
            If Target.Address = “C12” Or Target.Address = “C13” Or Target.Address = “C14” Or Target.Address = “C15” Or Target.Address = “C16” 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

          • neildor Davis says:

            ‘ I just swapped out Target.Address for Target.Column. Tested and working.

            Private Sub Worksheet_Change(ByVal Target As Range)
            Dim Oldvalue As String
            Dim Newvalue As String
            Application.EnableEvents = True
            On Error GoTo Exitsub
            If Target.Column = “C12” Or Target.Column = “C13” Or Target.Column = “C14” Or Target.Column = “C15” Or Target.Column = “C16” 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

        • eranfaraway says:

          Hi Sumit, This is really helpful but I’m not sure which “END IF” to remove. There’s three in the code, do I remove all three?

        • CJ Bergdahl says:

          Sumit – I was wonder if you could assist me. I have a column titled ‘Services’ and have created dropdown list in each cell. The worksheet has about 186 rows (and growing). I used this code to be able to select multiple and have them show up in each cell. I took out ‘IF Target.Address = “$C$2” Then and one END IF and not it works for all cells that has the dropdown list. HOWEVER, whenever I type anything in any other cell, it doubles/replicates what I already had in there plus what I was typing. I’m guessing that is because of the code I put in for the dropdowns. Could you help?

        • z says:

          thank a lot. your a life safer 🙂

  • >