How to Make Multiple Selection from Drop Down List in Excel

We’ll use a simple dataset to create a multi-selection drop-down.

Dataset for Examples of Multiple Selection from Drop Down List in Excel

Method 1 – Making Multiple Selections from an Excel Drop-Down List with Duplicate Options

Step 1 – Creating a Drop-Down List in Excel

Create a section in the Excel sheet for the drop-down.

Make Multiple Selection from Drop Down List with Duplicate Options in Excel

Select the cell where you want to add the drop-down. We selected Cell D5.
Go to the Data tab.
Select Data Validation.

The Data Validation dialog box will appear.
Select the Settings tab.
Click on the drop-down option for Allow.
Select List.

Create Drop Down List in Excel for Multiple Selection

Click on the range selector button to select the source.

Select the data range. We selected the cell range B5:B12.
Click on the button to the right of the selector box to add the selected range as Source.

Select OK.

A drop-down option will be added to your selected cell.

Click on the drop-down button.
Select an item from the list.

SElecting Items from Drop Down List for Multiple Selection

Here’s an example.

Select another item from the drop-down list.

The item you selected has replaced the previously selected item.

Step 2 – Writing VBA Code for Multiple Selection

Go to the Developer tab.
Select Visual Basic.

Write VBA Code for Multiple Selection from Drop Down List in VBA

The Visual Basic Editor will appear.
Right-click on the sheet where you want to select multiple options.
Select View Code.

Opening Worksheet to Write VBA Code for Multiple Selection from Drop Down List in Excel

A sheet module will appear.
Click on the first drop-down button.
Select Worksheet.

Click on the second drop-down button.
Choose Change.

You will see a Private Sub method template.

Insert the following code in the window (replacing the existing text).

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

VBA Code for Making Multiple Selection from Drop Down List in Excel

How Does the Code Work?

We have declared two strings called old_val and new_val.
We made the drop-down list in the D5 cell, that’s why the target address is D5. We have rechecked whether the cell is using data validation using Target.SpecialCells.
Once a value is selected, we turned off events (Application.EnableEvents = False) so changes don’t trigger the event again. We stored the selected item in the new_val.
After undoing the change, we have set the value to the old_val. Then, we check whether the old_val is empty. If it is (so only one value is selected), the function returns the new_val. Otherwise, it concatenates the old_val and new_val.

Save the code and go back to your worksheet.

Saving VBA Code for Multiple Selection from Drop Down List in Excel

Select a second item from the drop-down list.

You will see both of the selected items are showing in the cell.

Select an item that you have not selected so far.

You will see that this item is also added to the list.

Method 2 – Selecting Multiple Options with Unique Selection Only from an Excel Drop-Down List

Steps:

Add a drop-down list in your preferred cell by following Step 1 from Method 1.

Select Multiple Options with Unique Selection Only from Drop Down List in Excel

Open the Visual Basic Editor window and then open a Worksheet to write the VBA code by following Step 2 of Method 1.
Insert 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?

We declared 2 variables named old_val and new_val as String.
We used the Application.EnableEvents property to enable events for specified properties.
We used an IF Statement to add the items from the list.
We used another IF Statement to check if the item was selected before.
We used the InStr function in the IF statement to find one string within another.

Save the code and go back to your worksheet.
Select an item from the drop-down list.

You will see the item is added.
Select another item from the list.

You will see both of the items are added to the Selected Item.

Select an item that you have selected before from the drop-down list.

The item wasn’t added to the list.

Method 3 – Creating a Multiple Selection and Adding Items in New Lines in Excel

Steps:

Merge a few cells with Cell D5. We selected the cell range D5:D9.
Go to the Home tab.
Select Merge & Center.

Make Multiple Selection and Add Items on New Line in Excel

Add the drop-down list by following Step 1 from Method 1.

Open the Visual Basic Editor window and then open a worksheet to write the VBA code by following Step 2 of Method 1.
Use 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. Instead of the delimiter, we used vbNewLine to add the newly selected item in a new line.

Save the code and go back to your worksheet.
Select an item from the drop-down list.

You will see that the item will be added to the Selected Item.

Select another item from the drop-down list.

The new item is added to a new line.

We have added a few more items from the list and each of them is added to a new line.

Things to Remember

You must save the Excel file as Excel Macro–Enabled Workbook.

Download the Practice Workbook

Selecting Multiple Options from Drop Down.xlsm

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

Private Sub Worksheet_Change(ByVal Target As Range) Dim OldValue As String Dim NewValue As String On Error Resume Next ' Check if the change is within the validation range If Not Intersect(Target, Range("YourRange")) Is Nothing Then Application.EnableEvents = False ' Unprotect the worksheet ActiveSheet.Unprotect Password:="YourPassword" ' Store the old value OldValue = Target.Value ' Store the new value entered NewValue = Target.Text ' If the new value is not empty If OldValue <> "" Then If InStr(1, OldValue, NewValue) = 0 Then Target.Value = OldValue & ", " & NewValue Else Target.Value = NewValue End If End If Application.EnableEvents = True ' Reprotect the worksheet ActiveSheet.Protect Password:="YourPassword", UserInterfaceOnly:=True End If End Sub

50 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
  - Reply
    Rolands Jul 24, 2024 at 1:30 PM
    
    Hello Guria,
    Thank you so much! Your solution is exactly what I needed – this little correction for the code provided in “Method 2” above.
    I have several drop-down cells and the drop-down list (the same for the all drop-down cells), all of them in the same column E, so I replaced Target.Address with Target.Column = 5. It continues to work for newly created, Copy-Pasted entire rows.
    Thank you, love!
    - Shamima Sultana Jul 24, 2024 at 9:55 PM
      
      Hello Rolands,
      
      You are most welcome. Thank you for your feedback! I’m glad the solution worked for you. Replacing Target.Address with Target.Column = 5 is a great adjustment for your setup with multiple drop-down cells in column E. It’s wonderful to hear that it continues to work even for newly created and copy-pasted rows.
      
      We used Target.Address initially to target specific cells, making it easy to apply the method to designated areas. This approach provides precision and control, especially useful for varying drop-down list configurations.
      
      Regards
      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?

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:

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

Fritz Aug 13, 2024 at 1:56 AM

Hi Shakil,
I am trying your easy example one cell on multiple selection without repetitions, but after saving the vba code “worksheet, Change” and using the rich worksheet I still cannot multiple select.
Any idea of typical errors?
Many thanks,
Fritz
- Reply
  Shamima Sultana Aug 13, 2024 at 11:10 AM
  
  Hello Fritz,
  
  The VBA code is working fine in our end.
  
  Make sure to follow all the steps:
  Create the drop down list first then place the VBA code in that sheet.
  Double-check that the VBA code is correctly placed in the Sheet where the data validation with drop down list exists.
  Also, ensure that there are no conflicts with other existing macros or data validation settings. If the issue persists, try testing the code in a new worksheet to see if it works there.
  
  You can download our workbook for testing purpose: Selecting Multiple Options from Drop Down.xlsm
  
  Regards
  ExcelDemy
MP Sep 10, 2024 at 1:00 AM

Hello, All vba codes above work perfect for me so far. My dilemma is when i try to protect the worksheet the code stops working and it goes back to one choice on the data validation list now replaces the other. How can i fix this
- Reply
  Shamima Sultana Sep 10, 2024 at 12:27 PM
  Hello MP,
  
  Glad to hear that all the VBA code is working perfectly. To address the issue where the multiple selection drop-down stops working after protecting the worksheet, you need to modify the VBA code to allow edits to the specific cells while keeping the rest of the sheet protected. You can use the UserInterfaceOnly:=True parameter when protecting the sheet, which allows macros to make changes while maintaining protection for users.
  
  You can add UserInterfaceOnly:=True in the VBA code,
```
Private Sub Workbook_Open()
    Worksheets("YourSheetName").Protect Password:="YourPassword", UserInterfaceOnly:=True
End Sub
```
  It will run without unprotecting the sheet. Add this code in the Workbook_Open event to ensure the protection is applied every time the workbook is opened. Provide “YourSheetName” and “YourPassword” accordingly.
  
  Regards
  ExcelDemy

MP Sep 11, 2024 at 11:58 PM

Hi Shamima
Thank you for your quick response. Unfortunately, i have tried several times and that code is not working for me. I have tried several ways but it continues to stop the code from working.

Shamima Sultana Sep 12, 2024 at 11:48 AM

Hello MP,

You are most welcome. Modified the VBA code by unprotecting the worksheet at the start of the macro and reprotecting it at the end.

Unprotects the worksheet when a selection is made. Reprotects it afterward using UserInterfaceOnly:=True, allowing macros to work while the sheet remains protected.
Make sure to replace “YourPassword” with your actual password and “YourRange” with the cell range that contains the drop-down list.

Regards
ExcelDemy