How to Make Multiple Selection from Drop Down List in Excel

Written by Shakil Ahmed

Last updated: Dec 21, 2023

Depending on the circumstances you may need to select multiple values within a drop-down list. In this tutorial, I am going to show you how to make multiple selection from a drop-down list in Excel. For this session, I am using Office 365, feel free to use your preferred version.

Before diving into the session, let’s get to know about the dataset that is the base of the examples. Here I have several stationery elements, using these I will create a drop down list in Excel and make multiple selection from there.

Note that it is a simple dataset to keep things straightforward. In a practical scenario, you may encounter a much larger and more complex dataset.

1. Making Multiple Selection from Excel Drop Down List with Duplicate Options

In this example, I will explain how you can create a drop down list for multiple selections and select duplicate items. For this, you will have to create the drop down list first. But, a conventional drop down list always selects a single item. So, you will have to write a VBA code for multiple selections. Let’s explore the steps.

Step-01: Creating Drop Down List in Excel

In this step, I will create the drop down list for the dataset.

Firstly, create a section in the Excel sheet for adding the drop-down option.

Secondly, select the cell where you want to add the drop down list. Here, I selected Cell D5.
Thirdly, go to the Data tab.
Next, select Data Validation.

Next, the Data Validation dialog box will appear.
Select the Settings tab.
Then, click on the drop down option for Allow.
Afterward, select List.

Next, click on the marked button to select the source.

After that, select the data range. Here, I selected cell range B5:B12.
Next, click on the marked button to add the selected range as Source.

Then, select OK.

Finally, you will see that a drop down option is added to your selected cell.

Now, click on the drop down button.
Then, select an item from the list.

After that, you will see that the selected item is showing in the selected cell.

Next, select another item from the drop down list.

Here, you will see that the item you selected has replaced the previously selected item.

Step-02: Writing VBA Code for Multiple Selection

Now, I will show you how you can write a VBA code to make multiple selections from a drop down list in Excel.

In the beginning, go to the Developer tab.
Then, select Visual Basic.

Consequently, the Visual Basic Editor will appear.
Next, right-click on the sheet where you want to select multiple options.
Then, select View Code.

Now, a sheet module will appear.
Click on the marked drop-down button.
Then, select Worksheet.

Afterward, select the marked drop-down button.
Next, select Change.

Now, you will see a Private Sub will be created.

After that, write the following code.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim old_val As String
Dim new_val As String
On Error GoTo Exitsub
If Target.Address = "$D$5" Then
            If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
            GoTo Exitsub
            Else: If Target.Value = "" Then GoTo Exitsub Else
            Application.EnableEvents = False
            new_val = Target.Value
            Application.Undo
            old_val = Target.Value
            If old_val = "" Then
            Target.Value = new_val
            Else
            Target.Value = old_val & ", " & new_val
            End If
            End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

🔎 How Does the Code Work?

Here, I have declared two strings called old_val and new_val.
You can see I made the drop-down list in the D5 cell, that’s why the target address is D5. And in addition, I have rechecked whether the cell is using data validation or not using Target.SpecialCells.
Once a value is selected, I turned off events (Application.EnableEvents = False) so changes don’t trigger the event again. Then stored the selected item in the new_val.
After undoing the change, I have set the value to the old_val. Then check whether the old_val is empty or not. If empty (means only one value is selected), then return the new_val. Otherwise, concatenate the old_val and new_val.
Before ending the reset the event, so that I can change it if required.

Further, Save the code and go back to your worksheet.

Now, select a second item from the drop-down list.

Here, you will see both of the selected items are showing in the cell.

Now, select an item that you have selected before.

Finally, you will see that this item is also added to the list.

2. Selecting Multiple Options with Unique Selection Only from Excel Drop Down List

In this example, I will show you how you can select multiple unique items from a drop-down list in Excel. If you select an item that you have selected before then it will not add it to the selected item. Let’s see the steps.

Steps:

To begin with, add a drop-down list in your preferred cell by following Step-01 from Method-01.

Next, open the Visual Basic Editor window and then open a Worksheet to write the VBA code by following Step-02 of Method-1.
After that, write the following code in that Worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim old_val As String
Dim new_val As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Address = "$D$5" Then
  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
            GoTo Exitsub
  Else: If Target.Value = "" Then GoTo Exitsub Else
            Application.EnableEvents = False
            new_val = Target.Value
            Application.Undo
            old_val = Target.Value
            If old_val = "" Then
            Target.Value = new_val
            Else
            If InStr(1, old_val, new_val) = 0 Then
            Target.Value = old_val & ", " & new_val
            Else:
            Target.Value = old_val
            End If
            End If
  End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

🔎 How Does the Code Work?

Here, I declared 2 variables named old_val and new_val as String.
Then, I used the Application.EnableEvents property to enable events for specified properties.
Next, I used IF Statement to add the items from the list.
Afterward, I used another IF Statement to check if the item was selected before.
Then, I used the InStr function in the IF statement to find one string within another.
Further, I ended the IF Statements.
Finally, I ended the Sub Procedure.

Next, Save the code and go back to your worksheet.
Afterward, select an item from the drop down list.

Then, you will see the item is added.
Now, select another item from the list.

Here, you will see both of the items are added to the Selected Item.

Afterward, select an item that you have selected before from the drop-down list.

Now, you will see that the item is not added to the selected Item.

3. Creating Multiple Selection and Adding Items on New Lines in Excel

So far, I have found the items are separated by a comma. In this section, I will arrange the selected items in newlines. Let me show you the steps.

Steps:

In the beginning, I will merge a few cells with Cell D5.
To do that, select the cells you want to merge. Here, I selected cell range D5:D9.
Then, go to the Home tab.
After that, select Merge & Center.

After that, add the drop down list following Step-01 from Method-1.

Afterward, open the Visual Basic Editor window and then open a worksheet to write the VBA code by following Step-02 of Method-1.
Then, write the following code.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim old_val As String
Dim new_val As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Address = "$D$5" Then
  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
            GoTo Exitsub
  Else: If Target.Value = "" Then GoTo Exitsub Else
            Application.EnableEvents = False
            new_val = Target.Value
            Application.Undo
            old_val = Target.Value
            If old_val = "" Then
            Target.Value = new_val
            Else
            If InStr(1, old_val, new_val) = 0 Then
            Target.Value = old_val & vbNewLine & new_val
            Else:
            Target.Value = old_val
            End If
            End If
  End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

This code works in the same way as the previous code. But, instead of the delimiter use vbNewLine to add the newly selected item in a new line.

After that, Save the code and go back to your worksheet.
Next, select an item from the drop down list.

Then, you will see that the item will be added to the Selected Item.

After that, select another item from the drop down list.

Now, you will see that the new item is added to a new line.

In the following image, you can see that I have added a few more items from the list and each of them is added to a new line.

Things to Remember

If you are working with VBA then you must save the Excel file as Excel Macro–Enabled Workbook.

Download Practice Workbook

You are welcome to download the practice workbook from the link below.

Selecting Multiple Options from Drop Down.xlsm

Conclusion

That’s all for today. We have listed several approaches to make multiple selection in a drop-down list in Excel. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. Let us know any other methods that we have missed here.

<< Go Back to Create Drop-Down List in Excel | Excel Drop-Down List | Data Validation in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Sub Uniquelist() Set Rng = Range("B4:B11") Set Rng2 = Range("E4") Rng2.Cells(1, 1).Value = Rng.Cells(1, 1) Count = 1 For i = 2 To Rng.Rows.Count Found = False For j = 1 To Rng2.Rows.Count If Rng.Cells(i, 1).Value = Rng2.Cells(j, 1) Then Found = True Exit For End If Next j If Found = False Then Set Rng2 = Range(Cells(4, 5), Cells(4 + Count, 5)) Cells(4 + Count, 5) = Rng.Cells(i, 1) Count = Count + 1 End If Next i With Range("C14").Validation .Delete .Add Type:=xlValidateList, _ AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, Formula1:="=$E$4:$E$6" End With Range("$E$13:$E$15").ClearContents n = 0 For i = 1 To Rng.Rows.Count If Rng.Cells(i, 1) = Cells(14, 3).Value Then Cells(13 + n, 5) = Rng.Cells(i, 2) n = n + 1 End If Next i With Range("C15").Validation .Delete .Add Type:=xlValidateList, _ AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, Formula1:="=$E$13:$E$15" End With End Sub

Private Sub Worksheet_Change(ByVal Target As Range) Dim itemRange As Range Set itemRange = Me.Range("A4:B9") Dim dropdownRange As Range Set dropdownRange = Me.Range("C4:K38") If Not Intersect(Target, itemRange) Is Nothing Then dropdownRange.Cells.ClearContents Call CreateDropDown End If Dim old_val As String Dim new_val As String Application.EnableEvents = True On Error GoTo Exitsub If Not Intersect(Target, dropdownRange) Is Nothing Then If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then GoTo Exitsub Else: If Target.Value = "" Then GoTo Exitsub Else Application.EnableEvents = False new_val = Target.Value Application.Undo old_val = Target.Value If old_val = "" Then Target.Value = new_val Else If InStr(1, old_val, new_val) = 0 Then Target.Value = old_val & ", " & new_val Else: Target.Value = old_val End If End If End If End If Application.EnableEvents = True Exitsub: Application.EnableEvents = True End Sub Sub CreateDropDown() Dim dataRange As Range Dim dropdownRange As Range Dim itemArray() As String Dim i As Integer Dim cell As Range Set dataRange = Worksheets("Sheet1").Range("A5:B9") ReDim itemArray(1 To dataRange.Rows.Count * dataRange.Columns.Count) i = 1 For Each cell In dataRange itemArray(i) = cell.Value i = i + 1 Next cell Set dropdownRange = Worksheets("Sheet1").Range("C4:K38") For Each cell In dropdownRange With cell.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=Join(itemArray, ",") End With Next cell End Sub

42 Comments

Adrienne Roman Jan 4, 2022 at 2:43 AM

How can you apply this formula to a range of cells? I would like this to apply to a column of cells instead of a single cell.
- Reply
  Naimul Hasan Arif Aug 16, 2022 at 2:44 PM
  
  If you want to traverse through a column, you can wrap your code with loop which will continue throughout the column.
- Reply
  Darbi Apr 5, 2022 at 10:12 PM
  
  Same question here!
- Reply
  Francesco Mar 1, 2022 at 12:59 PM
  
  Hi! I have the same question!
Darbi Apr 5, 2022 at 10:33 PM

Hi! Same question! Anybody find an answer?
- Reply
  Naimul Hasan Arif Aug 16, 2022 at 2:46 PM
  
  You can wrap your code in a loop to iterate continuously across a column if you want to traverse it.
  - Reply
    Irene Aug 23, 2022 at 9:44 PM
    
    Hi, this was really helpful but please could you provide the code for the loop? Thank you!!
    - Naimul Hasan Arif Aug 25, 2022 at 3:35 PM
      
      I have tried the following VBA code for all the cells in D Column. I hope this is the thing you are looking for.
      
      Private Sub Worksheet_Change(ByVal Target As Range)
      
      Dim Oldvalue As String
      Dim Newvalue As String
      Dim mn As Range, pq As Range
      On Error GoTo Exitsub
      Set mn = Range(“D:D”)
      For Each pq In mn
      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
      Next pq
      Application.EnableEvents = True
      Exitsub:
      Application.EnableEvents = True
      End Sub
Ktrynha Apr 6, 2022 at 5:05 AM

I deleted the following lines of the VBA code:

If Target.Address = “$D$4” Then

the first “End If”
- Reply
  Naimul Hasan Arif Aug 16, 2022 at 3:34 PM
  
  Thanks for your insight and for trying something on your own.
Laura R Jun 6, 2022 at 8:14 PM

How do you deselect once selected using this code?
- Reply
  Naimul Hasan Arif Aug 16, 2022 at 2:49 PM
  
  You can use the Clear property of the cell. You need to apply Range(“__your cell__”).Clear to make the selection cleared.
- Reply
  MEJ27 Jun 14, 2022 at 11:49 PM
  
  I would like to know this, too
BJ Sanders Jul 18, 2022 at 11:58 PM

Its brilliant, but………. I want to also be able to add freeform text. So user selects a couple of standard texts from the drop down – all good
Then they click in to add specific text, hit enter and all of a sudden it duplicates the specific text already added.
I cant quite work out why this is happening.
- Reply
  Naimul Hasan Arif Aug 16, 2022 at 2:52 PM
  
  I’m afraid in an ideal scenario once you’ve used data validation with list of items to be selected, you will encounter an error since Excel anticipates values from that list only not any free text.
Naimul Hasan Arif Aug 25, 2022 at 3:53 PM

Thanks all of you guys for your comments.
I think so many people are facing the same problem of applying the code in a range of cells. In our article, the VBA code that we have shown only works for a fixed cell. So, I am going to give you guys a slightly modified VBA code that will work for a range of cells( i.e. entire D column).

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Oldvalue As String
Dim Newvalue As String
Dim mn As Range, pq As Range
On Error GoTo Exitsub
Set mn = Range(“D:D”)
For Each pq In mn
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
Next pq
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
- Reply
  Christofer Sep 1, 2022 at 1:12 PM
  
  Hello,
  that’s really interesting, but using this last code I get an error of syntax related to the following lines:
  
  Set mn = Range(“C:C”)
  …
  Target.Value = Oldvalue & “, ” & Newvalue
  
  Where is this syntax error?
  
  Thanks
  - Reply
    Naimul Hasan Arif Sep 4, 2022 at 2:11 PM
    
    Thanks for the appreciation.
    In my case, it works just fine. It is very tough for me to give a solution without analyzing your code related to the dataset. It would be helpful for me if you could provide me your code.
    - Naimul Hasan Arif Apr 12, 2023 at 4:29 PM
      
      Dear LUIS FERNANDO,
      Thanks for the appreciation. Also big thanks to you for sharing your insights on the query of CHRISTOFER.
      
      In response to your purpose, I have considered a case where I will have a drop-down with the names of the writers. Based on the writer selection, his books will appear in another drop-down just below the cell of the writer’s name.
      
      I have assigned the following code in a button where I will have writers’ names without repetition under Uniquelist column. I have created a drop-down in cell C14 with those values. Similarly, I have sorted the matched books under Sorted Books column with the writer’s name in C14.
      
      Sub Uniquelist() Set Rng = Range("B4:B11") Set Rng2 = Range("E4") Rng2.Cells(1, 1).Value = Rng.Cells(1, 1) Count = 1 For i = 2 To Rng.Rows.Count Found = False For j = 1 To Rng2.Rows.Count If Rng.Cells(i, 1).Value = Rng2.Cells(j, 1) Then Found = True Exit For End If Next j If Found = False Then Set Rng2 = Range(Cells(4, 5), Cells(4 + Count, 5)) Cells(4 + Count, 5) = Rng.Cells(i, 1) Count = Count + 1 End If Next i With Range("C14").Validation .Delete .Add Type:=xlValidateList, _ AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, Formula1:="=$E$4:$E$6" End With Range("$E$13:$E$15").ClearContents n = 0 For i = 1 To Rng.Rows.Count If Rng.Cells(i, 1) = Cells(14, 3).Value Then Cells(13 + n, 5) = Rng.Cells(i, 2) n = n + 1 End If Next i With Range("C15").Validation .Delete .Add Type:=xlValidateList, _ AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, Formula1:="=$E$13:$E$15" End With End Sub
      
      Now, select a writer name from drop-down and click on Sort button. You will have the related books’ name in the following drop-down and choose a book according to your preference.
    - Luis Fernando Apr 9, 2023 at 11:19 PM
      
      They are getting the error from your code because the quotation marks need to be changed to straight quotation marks ” ” but when they paste your code any place where there are marks come up as slanted quotation marks “ ”, and the vba code doesn’t recognize it.
      
      Your code is helpful but for my purposes it still doesnt provide the necessary solution. I need the additional selected options to appear in rows below the row of the cell being used with the DV.
    - Georgina Woods Nov 9, 2022 at 10:57 PM
      
      I am now getting the same code as Christopher.
      Syntax error in two lines of code:
      
      Set mn = Range(“G:G”)
      
      and
      
      Target.Value = Oldvalue & “;” & Newvalue
      
      I copied your code exactly, and only changed it to G:G to relate to my specific column needs.
      
      Many thanks,
    - Christofer Sep 5, 2022 at 2:17 PM
      
      Thank you Naimul,
      this is the code, I didn’t report it because I just copied and pasted your.
      
      I only changed the column from D to C:
      
      Private Sub Worksheet_Change(ByVal Target As Range)
      
      Dim Oldvalue As String
      Dim Newvalue As String
      Dim mn As Range, pq As Range
      On Error GoTo Exitsub
      Set mn = Range(“C:C”)
      For Each pq In mn
      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
      Next pq
      Application.EnableEvents = True
      Exitsub:
      Application.EnableEvents = True
      End Sub
Ann Hall Sep 5, 2022 at 5:16 AM

Thanks for your help! I am trying to use your code, but receive an error stating “Compile Error; Expected: End of statement” with the first Dim highlighted. What does that mean?
- Reply
  Rafi Oct 23, 2022 at 1:00 PM
  
  Hello ANN HALL,
  This may happen for different reasons.
  1. Putting two statements in a single line. Check whether this is encountered and send the second statement in a new line.
  2. Absence of parentheses.
  3. Lack of whitespace in front of the ampersand(&) interpret as a long variable. So, put space between the variable and operator(&).
sg Oct 19, 2022 at 11:24 PM

Hello – is there a way to turn off the error checking in the excel cell. I am creating a form for users and I don’t want them to see the “validation error” message.
- Reply
  Rafi Oct 23, 2022 at 2:26 PM
  
  Hello SG,
  You can customize the error message. In the Data Validation message box=> Error Alert icon, you can choose “Warning” style from the dropdown list. Now, if you try to input invalid data, the error message will show 3 options asking you whether you want to continue: Yes/No/Cancel/Help. Clicking “Yes” will allow you to proceed with the current value and will not show the error again.
Georgina Woods Nov 9, 2022 at 10:49 PM

Hello, I tried this however absolutely nothing seems to happen. The first time I attempted to click ”save” it asked me to save the spreadsheet in a macro enabled format (.xlsm) which I have done. I closed it and reopened it. But nada! The code is in there but it doesnt seem to have worked.

I literally copied/pasted the code exactly as you wrote it….assuming nothing needed to be amended in the code?

Thanks,
- Reply
  Naimul Hasan Arif Nov 10, 2022 at 11:40 AM
  
  Thanks for your valuable comment.
  A few factors might play a vital role in your problem.
  1. As the file contains VBA code, the file must be saved in “.xlsm” format.
  2. Don’t forget to change the ranges in your code. I have applied the code in D column. So, it’ll only be applicable in the D column.
  - Reply
    Helen Feb 9, 2023 at 2:22 PM
    
    Hi Naimul Hasan Arif – thanks for your patience in responding to everyone’s queries and issues :). Not sure if it will work for me, yet, but appreciate your efforts.
    - Shamima Feb 13, 2023 at 3:44 PM
      
      Hello, Helen!
      
      Thanks for your appreciation. To get more helpful information stay in touch with ExcelDemy.
      
      Regards
      ExcelDemy
Pratik Dec 10, 2022 at 11:30 AM

I have different lists in a single sheet where the VBA code is to be placed. Now I have to make multi selections from specific lists (ie having cells referring to different list to make multi selection from). Should I simply add different data validation for the respective cells?
- Reply
  Alok Dec 12, 2022 at 4:53 PM
  
  Dear PRATIK,
  Thanks very much for reading our articles. You have mentioned that you have different lists in a single sheet. Here, we will show how to declare data validation in different cells with different lists in a single sheet.
  
  Steps:
  ● Here, we have three different lists of mobile, laptop, and routers. Here, we will introduce different drop-down lists with multiple selections.
  
  ● Now, apply data validation as shown in the article already.
  ● We will choose Range B5:B11 at Cell G4, Range C5:C11 at Cell G5, and Range D5:D11 at Cell G6 for the data-validation source.
  ● Choose any item from the drop-down list of Cell G4.
  
  ● Similarly, choose items for Cell G5 and G6 from the drop-down list.
  
  ● Now, put the following VBA code in the VBA module. After that, save the VBA code.
  Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Oldvalue As String
  Dim Newvalue As String
  On Error GoTo Exitsub
  If Target.Column = 7 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, choose multiple options from the drop-down list.
  
  Here, we changed the Target.Address to Target.Column and put the column number. Also, need to mention the whole column will save the previous result.
Allison Dec 12, 2022 at 11:36 PM

Please Help. The First code works for my project however I can only get the drop down list to work for once cell.

I want to change the line

If Target.Address = “$AG$4” Then

to include cells AG4 to AG43

How do I get this to work? I tried $AG$4:$AG$43, as well as AND but they did not work
- Reply
  Guria Dec 14, 2022 at 5:21 PM
  
  Hello Allison,
  Glad that you shared your problem. I have looked at it and therefore suggest these solutions.
  If you want a drop-down list for some selected cells (AG4:AG43) then replace
  If Target.Address = "$AG$4" Then
  With
  If Target.Address = "$AG$4" Or Target.Address = "$AG$5" Or Target.Address = "$AG$6" …….. Or Target.Address = "$AG$43" Then
  If you want a drop-down list for the whole AG Column which is the 33rd column in the workbook then replace
  If Target.Address = "$AG$4" Then
  With
  If Target.Column = 33 Then
  I hope the solutions will help you. Let us know your feedback.
  Regards,
  Guria,
  Exceldemy
Patrik Dec 14, 2022 at 5:14 PM

@ Allison, try this one:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Not Intersect(Target, Range(“F:F”)) 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
Debbie Winsett Jan 31, 2023 at 4:53 AM

This is VERY helpful and just what I need except…I need the selected item to generate a total cost. Each item has a cost so how would that be totaled after selection? I would be very grateful for your help as I am stuck on this for a simple project. Thank you!
- Reply
  Meraz Al Nahian Mar 15, 2023 at 1:34 PM
  
  Hello Winsett, thanks for reaching out. In that case, use SUM function to calculate the total cost. Also, you can find the total cost automatically in the bottom right corner of your Excel sheet for the selected data. To sum the cost by criteria, use the SUMIF/SUMIFS function.
Maggie Mar 13, 2023 at 7:47 PM

I would like to be able to edit the list but when I delete an entry, I get a validation error.
- Reply
  Meraz Al Nahian Mar 15, 2023 at 2:26 PM
  
  Please mention which method you are following, this will help us to understand the reason why this error is occurring. Thank you.
Yannie Su Mar 22, 2023 at 3:59 PM

Use author’s second method and fill in the range

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Oldvalue As String
Dim Newvalue As String
Dim mn As Range, pq As Range
Application.EnableEvents = True
On Error GoTo Exitsub
Set mn = Range(“CC:CC”)
For Each pq In mn
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = “” Then GoTo Exitsub Else
Application.EnableEvents = False
new_val = Target.Value
Application.Undo
old_val = Target.Value
If old_val = “” Then
Target.Value = new_val
Else
If InStr(1, old_val, new_val) = 0 Then
Target.Value = old_val & “, ” & new_val
Else:
Target.Value = old_val
End If
End If
End If
Next pq
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
MIke Mar 7, 2024 at 10:47 PM

I can see all of the examples for multiple cells, but from what I can see they are all in the same column. What if I want to do multiple rows and columns as my range. I have tried “If (Target.Address = Range(“C4:K38″)) Then” but that does not work. Any ideas on what I am doing incorrect?
- Reply
  Lutfor Rahman Shimanto Mar 13, 2024 at 1:01 PM
  Hello MIke
  
  Thanks for visiting our blog and sharing an exciting query. It seems like you are attempting to expand the range of cells to which the multiple selection functionality applies.
  
  However, the line: “Target.Address = Range(“C4:K38”)” doesn’t quite work as expected because the “Target.Address” property returns the address of the changed cell, not the range as a whole.
  
  To check if a change occurred within a specific range of cells in Excel using VBA, use the Intersect method. It simply checks if any cells in the changed range overlap with your desired range. This way, your code responds accurately to changes within the specified range, regardless of the number of cells involved.
  
  I have developed a solution for your query. Follow these steps:
  Right-click on the sheet name tab.
  
  Click on View Code.
  
  Insert the following code in the sheet module and save it:
  Private Sub Worksheet_Change(ByVal Target As Range) Dim itemRange As Range Set itemRange = Me.Range("A4:B9") Dim dropdownRange As Range Set dropdownRange = Me.Range("C4:K38") If Not Intersect(Target, itemRange) Is Nothing Then dropdownRange.Cells.ClearContents Call CreateDropDown End If Dim old_val As String Dim new_val As String Application.EnableEvents = True On Error GoTo Exitsub If Not Intersect(Target, dropdownRange) Is Nothing Then If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then GoTo Exitsub Else: If Target.Value = "" Then GoTo Exitsub Else Application.EnableEvents = False new_val = Target.Value Application.Undo old_val = Target.Value If old_val = "" Then Target.Value = new_val Else If InStr(1, old_val, new_val) = 0 Then Target.Value = old_val & ", " & new_val Else: Target.Value = old_val End If End If End If End If Application.EnableEvents = True Exitsub: Application.EnableEvents = True End Sub Sub CreateDropDown() Dim dataRange As Range Dim dropdownRange As Range Dim itemArray() As String Dim i As Integer Dim cell As Range Set dataRange = Worksheets("Sheet1").Range("A5:B9") ReDim itemArray(1 To dataRange.Rows.Count * dataRange.Columns.Count) i = 1 For Each cell In dataRange itemArray(i) = cell.Value i = i + 1 Next cell Set dropdownRange = Worksheets("Sheet1").Range("C4:K38") For Each cell In dropdownRange With cell.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=Join(itemArray, ",") End With Next cell End Sub
  
  Return to the sheet and make the desired change, such as the following GIF.
  I am also attaching the solution workbook for better understanding; good luck.
  
  DOWNLOAD SOLUTION WORKBOOK
  
  Regards
  
  Lutfor Rahman Shimanto
  Excel & VBA Developer
  ExcelDemy