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:
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
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).
This has been one of the most popular Excel tutorials on this site. Since I get a lot of similar questions, I have decided to create an FAQ section at the end of this tutorial. So if you have any questions after reading this, please check out the FAQ section first.
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:
- Select the cell or range of cells where you want the drop-down list to appear (C2 in this example).
- Go to Data –> Data Tools –> Data Validation.
- In the Data Validation dialogue box, within the settings tab, select ‘List’ as Validation Criteria.
- In Source field, select the cells which have the items that you want in the drop down.
- 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).
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).
Try it Yourself.. Download the Example 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).
Try it Yourself.. Download the Example 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:
- 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.
- There should be a Project Explorer pane at the left (if it is not there, use Control + R to make it visible).
- Double click on Worksheet Name (in the left pane) where the drop-down list resides. This opens the code window for that worksheet.
- In the code window, copy and paste the above code.
- 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):
Try it Yourself.. Download the Example 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:
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
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
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.
This was working great and it just stopped working suddenly. Not sure why.
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.
Can we put multiple VBAs in the same sheet for different cells having separate dropdown lists.. I tried it does not work.
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.
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.
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
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?
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!
I have the same exact question.
i’ve been looking everywhere for a solution!! I even asked on the microsoft excel community for help.. maybe there will be a glimpse hope for us!! lol!
https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_winother-mso_2013_release/help-how-do-i-create-a-pivot-table-to-filter/9f274fe3-8dba-4b95-9df0-346e8cbe1502
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!
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!!
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.
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
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.
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.
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
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. :/
Same
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!
thanks so much! it works beautifully!
Just had one problem: why does it refrain me from editing other cells?
Thanks!
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?
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)?
What if my dropdown lists are in a different sheet than the dropdown data?
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
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
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??
I am having the same issue. Do I need to modify any of the code for my dropdown cells?
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!
@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?
I tried to manipulate the code to account for 2 columns to be able to select multiple items but having trouble. Can anyone help?
Hi, this worked perfectly, thank you.
But I cannot remove any item from the selection unless I delete the entire entry.
Any suggestions ?
hi sumit,
How to get the selected column header name for the selected cell.
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.
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!
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?
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
Is there a way to deselect something from the list if it was clicked by accident?
Never mind. Was able to look for a previous comment below
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?
let me know if you ever found a solution!
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.
Thanks a lot for the kind words.. Glad you found it useful 🙂
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.
Hey Karthi.. I don’t think it’s possible to apply any kind of formatting in the drop down.
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
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?
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?
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
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!
Nice. You just turn a “I don’t know if I can do that” into a 2 minute chore. You rock Sumit!
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.
I saved on format as reccomeneded. But when I reopen it it stops working. Any one know why ?
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.
Great. Thank you so much. I try to find support this solve
@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
Hi Sumit, is there a way for to vlookup code for the multiple drop down list selection for the above code
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
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.
I pose same question; did you get an answer
Dan, you don’t do that in the code. When you select your list in data validation you can select a different sheet. When you get into the data validation window you just click the formula area, then click the page, and the cell set you want to use
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.
Wonderful! Thank you!
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 !
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
Thanks for the quick reply !
Using XLS or XLSM format does not work for me but i succeeded using XLSXM format.
Thank a lot !
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.
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.
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!
What do I use for a range of cells in place of $C$2?
Dear Sumit,
You are THE man. Thank you.
That is all.
Thanks for commenting Heather! Glad it helped 🙂
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.
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)
You are a star…. thank you. Your help is amazing.
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. 🙂
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.
Sumit – this is brilliant! It has really helped me. Thank you.
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!
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.
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