How to Make Multiple Selection from Drop Down List in Excel

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

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.

2. Select Multiple Items (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 - Excel Drop Down List Multiple Selection

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

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 - Excel Drop Down List Multiple Selection

The cell will gain more height.

Merged cell - Excel Drop Down List Multiple Selection

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 - Excel Drop Down List Multiple Selection

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 - Excel Drop Down List Multiple Selection

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

Select another item - new line - Excel Drop Down List Multiple Selection

You will find the two items are in different lines.

Items separated by new line - Excel Drop Down List Multiple Selection

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 - Excel Drop Down List Multiple Selection

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.

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.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo