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). 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: 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): 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: 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: 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. 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.
Creating Multiple Drop-down Lists in Excel without Repetition. Display Main and Subcategory in Drop Down List in Excel. Create an Excel Drop Down list with Search Suggestions. Get Multiple Lookup Values in a Single Cell. How to Remove Drop-Down List in Excel?
I don’t know what I am doing wrong. I cannot make this work. Dim Oldvalue As String Dim Newvalue As String Application.EnableEvents = True On Error GoTo Exitsub If Not Intersect(Target, Range(“M6:M20”)) 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 = “” Or Oldvalue = “Please select!” 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 Application.EnableEvents = True Exitsub: If ActiveCell.Value = “” Then Target.Value = “Please select!” End If Application.EnableEvents = True Exit Sub End If End Sub 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: RemoveLocation = InStr(1, OldValue, NewValue) ItemLength = Len(NewValue) If Len(OldValue) Len(NewValue) Then Target.Value = Left(OldValue, RemoveLocation – 1) & Mid(OldValue, RemoveLocation + ItemLength + 2) End If End If ‘remove leading and trailing spaces en comma’s Do While True If Left(Target.Value, 1) = ” ” Or Left(Target.Value, 1) = “,” Then Target.Value = Mid(Target.Value, 2) Else Exit Do End If Loop Do While True l = Len(Target.Value) If Right(Target.Value, 1) = ” ” Or Right(Target.Value, 1) = “,” Then Target.Value = Left(Target.Value, l – 1) Else Exit Do End If Loop End If I need to know how to apply this in multiple columns thank you so much for your help Would you have any further suggestions? Thank you Maybe you can post your code here then I can have a look. Thanks! I need help modifying the code. I want to appear “Please select” in the Target.Address/Target.Column. I tried but i failed. 🙁 If Target.Address = “$C$2” Then with this line: If Target.Column = 5 Then My validation list is on another tab. Is that the problem? Thank you for your great assistance in multiple selection drop down list through a VBA code. I have used it and it is working fine for me. Only, change in the output I need per my project’s requirement is, can we have the selections in either new rows or columns rather new line? It would be a really helpful if that is possible. If Target.Column = 3 Then The above code does not work unless I put I write with with quotation marks –> If Target.Column = “3” Then Now I can choose multiple items, however, I get a Data verification error. When I use the debugger it points to: Application.EnableEvents = True Your code is a life saver, Thank you. It saved a lot of re-work for me. However in my case, the data is getting duplicated in case I am selecting one item multiple times. Any suggestions? Excel Version: MS Excel for Office 365 MSO (16.0.11929.20708) 64-bit List of values: One Two Three Four Five Output (upon multiple selection): One, Two, Five, One Regards, Dj If Target.Column = 9 Or Target.Column = 10 Or Target.Column = 11 Or Target.Column = 12 Or Target.Column = 13 Or Target.Column = 14 Or Target.Column = 15 Or Target.Column = 16 Or Target.Column = 17 Or Target.Column = 18 Or Target.Column = 19 Or Target.Column = 20 Then Thoughts? What if the target at different worksheet? Tq If Target.EntireColumn.Address = “$G:$G” And Mid(Target.Address, 4, Len(Target.Address) – 3) > 1 Then (assuming you want this validation rule to apply to the range of cells c2 = c10) If Left(Target.Address, 2) = “$C” And Mid(Target.Address, 4, 1) > 1 And Mid(Target.Address, 4, 1) < 11Then I just tested this out successfully in my spreadsheet If Target.EntireColumn.Address = “$G:$G” And Mid(Target.Address, 4, Len(Target.Address) – 3) > 1 Then In the multiple drop down I need each item selected to be numbered as (1), (2) etc.. Can someone help me by providing the VBA code for the same Similar to Following Table:- …. A Q ……..AR 24…Blank….Blank 25…A……….B 26…C……….D 27…E……….F etc. All Values are Text. In column I & J I have multi select boxes. In Column ” I ” Drop Down List from Table A Q 24 to A Q 47 While in Column ” J ” from table AR 24 to AR 47. These boxes give comma separated values. I want in column J to select corresponding automatic comma separated values as per the table. Such as in above example:- If I select In column” I ” A,C,E The column ” J ” should give B,D,F automatically Can you suggest me the proper code ? Thank you. Prakash Kulkarni Question: if you want to amend/remove a selection you need to remove and start again. Any way when you select the same again, it simply removes that selection? If Target.Column = 4 Or Target.Column = 6 Then If oldVal = “” Or newVal = “” Then ‘Do nothing Else lUsed = InStr(1, oldVal, newVal) If lUsed > 0 Then If Right(oldVal, Len(newVal)) = newVal Then Target.Value = Left(oldVal, Len(oldVal) – Len(newVal) – 1) Else Target.Value = Replace(oldVal, newVal & Chr(10), “”) ‘Allows multiple items to be selected and to deselect End If Else Target.Value = oldVal & Chr(10) & newVal Should this be in addition to the above code or replace the entire code and use this one? I have the same issue. I was wondering if you got the fix for this that you wouldn’t mind sharing. How to do the same in Google sheets? let’s say I have input the following data in C2 Two, Three C3 Three, Four C4 One, Four C5 Two then I discover I cannot count the data by “Filter” what I can do so that I can count out : One x 1 Two x 2 Three x 2 Four x 2 thank you very much for your help !!!! ruby from Japan If anyone would like to remove an item from the list by clicking on it again, change the following lines of code: from Target.Value = Oldvalue & ” ” & Newvalue to Target.Value = Trim(Oldvalue & ” ” & Newvalue) and from Target.Value = Oldvalue to Target.Value = Trim(Replace(Oldvalue, Newvalue, “”)) Don’t forget to Save your workbook as “Excel macro-enabled workbook”, so you don’t lose your VBA code every time. iPos = InStr(1, Oldvalue, Newvalue) If iPos = 0 Then Target.Value = Trim(Oldvalue & “, ” & Newvalue) Else: If iPos = 1 Then If Target.Value = Newvalue Then Target.Value = “” Else Tmpvalue = Newvalue & “,” Target.Value = Trim(Replace(Oldvalue, Tmpvalue, “”)) End If Else Tmpvalue = “, ” & Newvalue Target.Value = Trim(Replace(Oldvalue, Tmpvalue, “”)) End If End If Because I am working in the construction industry and for my labor allocation I will be selecting the labor names from a list. However, I want to display only the count. The names of labors selected will be displayed upon clicking the cell. Thanks alot. I am very inexperient in VBA, can help me? Best regards. How would one extend this to work when sharing via O365 ? I’ve tried& it doesn’t work. Not sure if it’s a limitation from O365 or if I need additional steps. This worked for me to apply to a whole column, but I have not figured out how to apply to multiple columns as yet. If Target.Column = 2 or Target.Column = 3 Then This will make this work for column 2 and 3 Code below does not work: If Target.Column = 3 or Target.Column = 14 or Target.Column = 15 Then And following question, if in sheet 1 cell A1 I has vba code with multiple selections, can the same selections be returned in sheet 2 cell B2? That way the data auto populates and does not require to input the vba code for sheet 2 and manually select the second time. Thank you for taking the time and educating us. Thank you for this quick fix, great! How do I lock the values of a cell (data range) when I make a table of many rows? Each new row creates a new range of data values, selection +1 for each new row. Thx! Thanks a lot Do you have the code that you could share? I am sure there is a trick to it. Vanessa 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. 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? on the other hand, if someone types the name of the new stakeholder first and then select from the drop-down list, I get: New stakeholder, Finance Is there any way to fix this to make sure we get the same results whether people type before or after selecting from the existing list? Thanks again. Thanks! 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 Thanks for the great site and sharing your Knowledge! 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! 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. 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 . 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 . When making multiple selections, how do you then delete a selection you may have accidentally clicked? 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? 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 Tia. /Dirk 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. 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 How do I protect the code form getting change by other user Thanks! 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. 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 Thank you 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? 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 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 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 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!!! 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 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 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. 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 In the target column did the Data -> Data validation -> List to the column holding the list (X1-155) What am I missing? 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 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! 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 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 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. 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 Thanks 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) 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! 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 If Target.column = 3 Then is it possible to use a reference to the table and column? Table1[column1]? Do you know how to fix this? Thanks so much for your help! If I have to delete an entry from the list, it does not behave the way it should. Have you tried that? 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 < 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. Dara from Cambodia 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 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 How can I send you the file? 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. Thanks 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. Angie Now the drop down will work for all the cells in Column C 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 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? Change the references to what you want. 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. If you want it for an entire column (say column D), make it: If Target.Column = 4 Then 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? 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 If Target.Address = “$C$2” Then with If Target.Column = 3 Then 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 This might help. 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 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. If Target.Address = “$C$2” Then Also remove one the END IF from the end of the code. If Target.Address = “$C$2” Then with If Target.Column = 7 And Target.Row > 4 And Target.Row < 54 Then 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 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 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 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
