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.
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.
Make Multiple Selection from Drop Down List.xlsm
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.
B4:B11 is the range that holds the stationery elements. Now you will find the drop-down list.
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).
Now, if we select another item, let’s 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. Let’s 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, 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
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 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.
The cell will gain more height.
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
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
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.
The post How to Make Multiple Selection from Drop Down List in Excel appeared first on ExcelDemy.