How to Make Multiple Selection from Drop Down List in Excel (3 Ways)

Depending on the circumstances you may need to select multiple values within a drop-down list. In this tutorial, we are going to show you how to make multiple selection in a drop-down list. For this session, we are using Excel 2019, 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 our examples.

Dataset - Excel Drop Down List Multiple Selection

Here we have several stationery elements, using these we will create a drop-down list and select multiple items there.

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

Practice Workbook

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

Multiple Selection in a Drop-Down List

First of all, we need to create a drop-down list on the basis of our stationeries. Let’s create it quickly. Don’t hesitate to visit the article regarding the making of a drop-down list.

In the Data Validation dialog box select the LIST data type and insert the cell range of the items.

Data Validation dialog box - Excel Drop Down List Multiple Selection

B4:B11 is the range that holds the stationery elements. Now you will find the drop-down list.

Drop-Down list - Excel Drop Down List Multiple Selection

Read more: Excel Drop Down List Depending on Selection

1. Select Multiple Items (Allows Duplicate Selection)

A conventional drop-down list always selects a single item. Here you can see, we have selected Pen from the list (image below).

Single selection - Excel Drop Down List Multiple Selection

Now, if we select another item, let’s say Pencil

Attempt to select another - Excel Drop Down List Multiple Selection

then it will replace the previous value. Only Pencil will remain selected.

New replace earlier selection - Excel Drop Down List Multiple Selection

To select multiple items, we need to use the VBA code. Open the Microsoft Visual Basic for Applications window (press ALT + F11 to open it).

Now double click on the worksheet name or number where you want to select multiple items within the drop-down list. You will find the code window for that particular sheet.

Code window - Excel Drop Down List Multiple Selection

Here, is the code window for Sheet2 in our workbook (we have the drop-down list in this sheet).

Once the code window is opened, insert the following code there

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Oldvalue As String

Dim Newvalue As String

On Error GoTo Exitsub

If Target.Address = "$D$4" 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

 

Code - allows duplicates - Excel Drop Down List Multiple Selection

Save the code, and now try to select values in the drop-down list.

Select items - Excel Drop Down List Multiple Selection

Having selected Pencil, we are going to select another item Notebook. And you can see, we found both the items (image below).

Multiple selection - Excel Drop Down List Multiple Selection

This code will allow us to repeat the selection. Let’s say if we select Pencil again,

Duplicate selection - Excel Drop Down List Multiple Selection

we will find the item again in the selection box.

Duplicates in selection - Excel Drop Down List Multiple Selection

Code Explanation

We have declared two strings Oldvalue and Newvalue.

You can see we made the drop-down list in the D4 cell, that’s why our target address is D4. And in addition, we have rechecked whether the cell is using data validation or not using Target.SpecialCells.

Once a value is selected, we turned off events (Application.EnableEvents = False) so changes don’t trigger the event again. Then stored the selected item into the Newvalue.

After undoing the change, we have set the value into the Oldvalue. Then check whether the Oldvalue is empty or not. If empty (means only one value is selected), then return the Newvalue. Otherwise, concatenate the Oldvalue and Newvalue.

Before ending the reset the event, so that we can change if required.

Read more: Multiple Dependent Drop-Down List Excel VBA

2. Select Multiple Items From Drop Down List (Unique Selection Only)

In the earlier section, we have seen the multiple selections where repetition was allowed. If you don’t want that, then follow this section.

For convenience, we used a separate sheet for this demonstration. This time we are at Sheet3. Write the following code in the code window for this sheet.

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 = "$D$4" 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

 

Code - unique only - Excel Drop Down List Multiple Selection

Is there any difference compared to the earlier code! Have a closer look, you will be able to spot the slight difference.

Here we have used a VBA function called INSTR. The INSTR function returns the position of the first occurrence of a substring in a string. Visit this INSTR article for further information.

Using this logical operation with InStr(1, Oldvalue, Newvalue) = 0, we have checked whether the values are found or not. If the logical operation returns TRUE (not found earlier) then it allows to select the item and concatenate with the earlier value.

Save the code and now try to select an item that has already been selected.

Only unique selection

Here we have already selected Pencil, if we want to select that again, we can’t. It doesn’t allow duplicate values.

Read more: How to Create a Drop Down List From Another Sheet in Excel

3. Select Items in Newline

So far, we have found the items are separated by a comma. In this section, we will arrange the selected items in newlines.

For simplicity, we are merging a few cells with the D4 cell. To do that, select the cells you want to merge and click Merge & Center from the Alignment section of the Home tab.

Merge & center

The cell will gain more height.

Merged cell

Now, let’s look at the code for separating items through newline. Use the following code

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 = "$D$4" Then

  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then

    GoTo Exitsub

  Else: If Target.Value = "" Then GoTo Exitsub Else

    Application.EnableEvents = False

    Newvalue = Target.Value

    Application.Undo

    Oldvalue = Target.Value

      If Oldvalue = "" Then

        Target.Value = Newvalue

      Else

        If InStr(1, Oldvalue, Newvalue) = 0 Then

            Target.Value = Oldvalue & vbNewLine & Newvalue

      Else:

        Target.Value = Oldvalue

      End If

    End If

  End If

End If

Application.EnableEvents = True

Exitsub:

Application.EnableEvents = True

End Sub

 

Code - new line

The only difference from the previous code is that this time we used vbNewLine in between OldValue and NewValue.

vbNewLine provides a new line between the items.

Now select the items.

Single item in drop -down list

We select an item Pen that is showing in the image above. Now select another element.

Select another item

You will find the two items are in different lines.

Items separated by new line

Here we have two values, that are in two different lines. Selecting another value will add that to another line. Every value will be in a new line.

Items separated by new line

Note that if you want another delimiter to separate the items, use that within double quotes in place of vbNewline.

Conclusion

That’s all for today. We have listed several approaches to make multiple selections in a drop-down list. 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.


Further Readings

Shakil Ahmed

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

20 Comments
  1. 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.

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

      • 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

  3. I deleted the following lines of the VBA code:

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

    the first “End If”

  4. How do you deselect once selected using this code?

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

  6. Reply
    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

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

        • 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

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

Leave a reply

ExcelDemy
Logo