One of the most beneficial Excel features is drop-down lists. They enable you to compile a collection of things you can choose from. When you have a long list of options to choose from, this is very useful.
Excel drop-down lists restrict users from choosing only one choice, though.
This might be a problem if your spreadsheet requires you to select one or more values from a list of choices.
The only method is to use a VBA code that adds the selected value to the existing value whenever you select it.
I’ll demonstrate multiple selections in Excel from a drop-down list in this tutorial. I’m using Office 365 for this session, but feel free to use your preferred version.
This Tutorial Covers:
- How to Use a Drop Down List to Make Multiple Choices
- Making the Drop Down Chart in Excel
- Multiple Choices in a Drop-Down List are Supported by VBA (with repetition)
- Multiple Choices in a Drop-Down List are Supported by VBA (without repetition)
- Where to Place the VBA Code
- Frequently Asked Questions (FAQs)
- How to Use a Drop Down List to Make Multiple Choices?
This tutorial will explain how to select multiple options from an Excel drop down list (with repetition and without repetition).
A drop-down list with multiple selections can be made in two steps:
- Setting up the drop-down menu.
- Applying the VBA code to the back end.
- Making the Drop Down List in Excel:
Step 1: Choose the cell or range of cells in which the drop-down menu should show (C2 in this example).
Step 2: Select “Data Validation” from the list of options in the “Data Tools” part of the “Data” tab.
Step 3: Choose “List” as the validation criteria in the “Data Validation” dialogue box’s “Settings” menu. Choose the cells in the “Source” field that contain the drop-down menu options you want to use. Select OK.
Now, cell C2 has a drop-down list with the titles of the countries in A2 to A11 on it.
Right now, you can choose one item from a drop-down list at a moment (as shown below).
We must add the VBA code in the back end to enable this drop-down to enable multiple selections.
The following two sections of this tutorial provide the VBA code to enable multiple selections in the drop-down list (with and without repetition).
- Multiple Choices in a Drop-Down List are Supported by VBA (with repetition):
This Excel VBA code will allow us to select multiple items from the drop-down list (allowing repetitions in selection):
Sub Worksheet_Change(ByVal Target As Range)
Dim old_val As String
Dim new_val As String
On Error GoTo Exitsub
If Target.Address = “$C$2” 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
Now that you have this code, you must insert it into a module in the VB Editor (as demonstrated in the section below titled “Where to Place the VBA Code”).
This code will enable you to make multiple selections in the drop-down menu once it is added to the backend (which will be covered later in this tutorial).
Just keep in mind that if you choose an item more than once, it will be entered again (repetition is allowed).
- Multiple Choices in a Drop-Down List are Supported by VBA (without repetition):
Many people have been inquiring about the code that allows them to select multiple items from a drop-down list without having to repeat themselves.
The following code will ensure that each object can only be chosen once to prevent duplications:
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 = “$C$2” 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
You must now insert this code into a package in the VB Editor (as shown in the next section of this tutorial).
You can choose multiple items from the drop-down list by using this code. You will only be able to choose an item once, though. The second time you tried to select it, nothing would happen. This VBA code prohibits repetition.
2. Where to Place the VBA Code?
Before using this code in Excel, it must be placed in the back end so that it is triggered whenever the drop-down option changes.
How to Use VBA code in Excel is shown below:
Step 1: Click “Visual Basic” under the “Developer” Tab (or use the keyboard shortcut Alt + F11). The “Visual Basic Editor” will appear after that.
Step 2: A pane labeled “Project Explorer” should be present on the left; if not, press Control + R to make it accessible. Where the drop-down selection is located (in the left pane), double click on Worksheet Name. This starts the worksheet’s code window. Copy the code above, then put it in the code window.
Close the VB Editor completely.
You can choose multiple options when you return to the drop-down menu at this point (as shown earlier section).
Note: You must save the workbook with an.xls or.xlsm suffix because we will be using VBA code to complete this.
3. Frequently Asked Questions (FAQs):
In order to address some of the frequently asked queries regarding this tutorial and the VBA code, I’ve made this section.
Question 1: Cell C2 is the only one with capability in the VBA code. For other cells, how do I get it?
Ans: You must change the VBA code in the backend to enable this multiple selection drop-down in other cells. Replace the following line in the code if you want to get this for D2, D3, and D4.
If Target.Address = “$D$2” Then
with this line:
If Target.Address = “$D$2” Or Target.Address = “$D$3” Or Target.Address = “$D$4” Then
Question 2: In the entire column “C,” I need to make multiple drop-down menus. How do I enable multi-select capability for every cell in the columns?
Ans: Replace the following line in the code to enable multiple drop-down selections for the full column:
If Target.Address = “$C$2” Then
with this line:
If Target.Column = 3 Then
Along the same lines, use the following line if you want this capability in columns C and D:
If Target.Column = 3 or Target.Column = 4 Then
Question 3: I need to make several drop-down menus in a succession. How can I go about this?
Ans: The following line of code needs to be replaced in order to make drop-down lists with multiple selections in a row, say the third row:
If Target.Address = “$C$3” Then
with this line:
If Target.Row = 3 Then
Similarly, use the code below if you want this to function for more than one row, like the third and fourth rows:
If Target.Row = 3 or Target.Row = 4 Then
Question 4: The multiple options are currently separated by commas. How can I alter this so that these are separated by spaces? (or any other separator).
Ans: You must change the following line of VBA code in order to divide these with a separator other than a comma:
Target.Value = Oldvalue & “, ” & Newvalue
with this line of VBA code:
Target.Value = Oldvalue & ” ” & Newvalue
Similarly, you can use the following piece of code to replace a comma with another character, such as |:
Target.Value = Oldvalue & “| ” & Newvalue
Question 5: Is it possible to have each option in its own line within a single cell?
Ans: Certainly, you can. You must modify the following line of VBA code to achieve this:
Target.Value = Oldvalue & “, ” & Newvalue
with this line of code:
Target.Value = Oldvalue & vbNewLine & Newvalue
In the same cell, “vbNewLine” adds a new line. As a result, each choice you make from the drop-down will start a new string.
Question 6: Is it possible to use the numerous selection feature in a protected sheet?
Ans: Certainly, you can. You must take two actions in order to complete this:
- After the DIM instruction, insert the following line into the code:
Me.Protect UserInterfaceOnly:=True
- When you protect the complete sheet, you should check to see that the cells with drop-down menus and multiple selection functionality are not locked.
For ready-to-use Dashboard Templates:
