How do you select multiple items in a 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, lets get to know about the dataset that is the base of our examples.

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. Lets create it quickly. Dont 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.

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

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

Now, if we select another item, lets say Pencil

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

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.

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

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

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

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

we will find the item again in the selection box.

Code Explanation

We have declared two strings Oldvalue and Newvalue.

You can see we made the drop-down list in the D4 cell, thats 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 dont 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 dont 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

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.

Here we have already selected Pencil, if we want to select that again, we cant. It doesnt 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.

The cell will gain more height.

Now, lets 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

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.

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

You will find the two items are in different lines.

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.

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

Conclusion

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