- \n
- How to Use a Drop Down List to Make Multiple Choices<\/strong><\/a>\n
- \n
- Making the Drop Down Chart in Excel<\/li>\n
- Multiple Choices in a Drop-Down List are Supported by VBA (with repetition)<\/li>\n
- Multiple Choices in a Drop-Down List are Supported by VBA (without repetition)<\/li>\n<\/ul>\n<\/li>\n
- Where to Place the VBA Code<\/strong><\/a><\/li>\n
- Frequently Asked Questions (FAQs)<\/strong><\/a><\/li>\n<\/ol>\n
- \n
- How to Use a Drop Down List to Make Multiple Choices?<\/strong><\/li>\n<\/ol>\n
This tutorial will explain how to select multiple options from an Excel drop down list (with repetition and without repetition).<\/p>\n
A drop-down list with multiple selections can be made in two steps:<\/p>\n
- \n
- Setting up the drop-down menu.<\/li>\n
- Applying the VBA code to the back end.<\/li>\n<\/ol>\n
- \n
- Making the Drop Down List in Excel:<\/strong><\/li>\n<\/ul>\n
Step 1:<\/strong> Choose the cell or range of cells in which the drop-down menu should show (C2 in this example).<\/p>\n
![\"Multiple](\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Multiple-Selections-option-in-a-Drop-Down-List-1.png\")
<\/p>\nStep 2:<\/strong> Select “Data Validation” from the list of options in the “Data Tools” part of the “Data” tab.<\/p>\n
![\"Multiple](\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Multiple-Selections-option-in-a-Drop-Down-List-2.png\")
<\/p>\nStep 3:<\/strong> 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.<\/p>\n
![\"Multiple](\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Multiple-Selections-option-in-a-Drop-Down-List-3.png\")
<\/p>\nNow, cell C2 has a drop-down list with the titles of the countries in A2 to A11 on it.<\/p>\n
![\"Multiple](\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Multiple-Selections-option-in-a-Drop-Down-List-4.png\")
<\/p>\nRight now, you can choose one item from a drop-down list at a moment (as shown below).<\/p>\n
![\"Multiple](\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Multiple-Selections-option-in-a-Drop-Down-List-5.png\")
<\/p>\nWe must add the VBA code in the back end to enable this drop-down to enable multiple\u00a0selections.<\/p>\n
The following two sections of this tutorial provide the VBA code to enable multiple selections in the drop-down list (with and without repetition).<\/p>\n
- \n
- Multiple Choices in a Drop-Down List are Supported by VBA (with repetition):<\/strong><\/li>\n<\/ul>\n
This Excel VBA code will allow us to select multiple items from the drop-down list (allowing repetitions in selection):<\/p>\n
Sub Worksheet_Change(ByVal Target As Range)<\/strong><\/p>\n
Dim old_val As String<\/strong><\/p>\n
Dim new_val As String<\/strong><\/p>\n
On Error GoTo Exitsub<\/strong><\/p>\n
If Target.Address = “$C$2” Then<\/strong><\/p>\n
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then<\/strong><\/p>\n
GoTo Exitsub<\/strong><\/p>\n
Else: If Target.Value = “” Then GoTo Exitsub Else<\/strong><\/p>\n
Application.EnableEvents = False<\/strong><\/p>\n
new_val = Target.Value<\/strong><\/p>\n
Application.Undo<\/strong><\/p>\n
old_val = Target.Value<\/strong><\/p>\n
If old_val = “” Then<\/strong><\/p>\n
Target.Value = new_val<\/strong><\/p>\n
Else<\/strong><\/p>\n
Target.Value = old_val & “, ” & new_val<\/strong><\/p>\n
End If<\/strong><\/p>\n
End If<\/strong><\/p>\n
End If<\/strong><\/p>\n
Application.EnableEvents = True<\/strong><\/p>\n
Exitsub:<\/strong><\/p>\n
Application.EnableEvents = True<\/strong><\/p>\n
End Sub<\/strong><\/p>\n
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”).<\/p>\n
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).<\/p>\n
Just keep in mind that if you choose an item more than once, it will be entered again (repetition is allowed).<\/p>\n
![\"Multiple](\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Multiple-Selections-option-in-a-Drop-Down-List-6.png\")
<\/p>\n- \n
- Multiple Choices in a Drop-Down List are Supported by VBA (without repetition):<\/strong><\/li>\n<\/ul>\n
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.<\/p>\n
The following code will ensure that each object can only be chosen once to prevent duplications:<\/p>\n
Private Sub Worksheet_Change(ByVal Target As Range)<\/strong><\/p>\n
Dim old_val As String<\/strong><\/p>\n
Dim new_val As String<\/strong><\/p>\n
Application.EnableEvents = True<\/strong><\/p>\n
On Error GoTo Exitsub<\/strong><\/p>\n
If Target.Address = “$C$2” Then<\/strong><\/p>\n
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then<\/strong><\/p>\n
GoTo Exitsub<\/strong><\/p>\n
Else: If Target.Value = “” Then GoTo Exitsub Else<\/strong><\/p>\n
Application.EnableEvents = False<\/strong><\/p>\n
new_val = Target.Value<\/strong><\/p>\n
Application.Undo<\/strong><\/p>\n
old_val = Target.Value<\/strong><\/p>\n
If old_val = “” Then<\/strong><\/p>\n
Target.Value = new_val<\/strong><\/p>\n
Else<\/strong><\/p>\n
If InStr(1, old_val, new_val) = 0 Then<\/strong><\/p>\n
Target.Value = old_val & “, ” & new_val<\/strong><\/p>\n
Else:<\/strong><\/p>\n
Target.Value = old_val<\/strong><\/p>\n
End If<\/strong><\/p>\n
End If<\/strong><\/p>\n
End If<\/strong><\/p>\n
End If<\/strong><\/p>\n
Application.EnableEvents = True<\/strong><\/p>\n
Exitsub:<\/strong><\/p>\n
Application.EnableEvents = True<\/strong><\/p>\n
End Sub<\/strong><\/p>\n
You must now insert this code into a package in the VB Editor (as shown in the next section of this tutorial).<\/p>\n
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.<\/p>\n
![\"Multiple](\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Multiple-Selections-option-in-a-Drop-Down-List-7.png\")
<\/p>\n2. Where to Place the VBA Code?<\/strong><\/p>\n
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.<\/p>\n
How to Use VBA code in Excel is shown below:<\/strong><\/p>\n
Step 1:<\/strong> Click \u201cVisual Basic\u201d under the \u201cDeveloper\u201d Tab (or use the keyboard shortcut Alt + F11). The \u201cVisual Basic Editor\u201d will appear after that.<\/p>\n
![\"Multiple](\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Multiple-Selections-option-in-a-Drop-Down-List-8.png\")
<\/p>\nStep 2:<\/strong> 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.<\/p>\n
Close the VB Editor completely.<\/p>\n
![\"Multiple](\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/03\/Multiple-Selections-option-in-a-Drop-Down-List-9.png\")
<\/p>\nYou can choose multiple options when you return to the drop-down menu at this point (as shown earlier section).<\/p>\n
Note:<\/strong> You must save the workbook with an.xls or.xlsm suffix because we will be using VBA code to complete this.<\/p>\n
3. Frequently Asked Questions (FAQs):<\/strong><\/p>\n
In order to address some of the frequently asked queries regarding this tutorial and the VBA code, I’ve made this section.<\/p>\n
Question 1: Cell C2 is the only one with capability in the VBA code. For other cells, how do I get it?<\/strong><\/p>\n
Ans:<\/strong> 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.<\/p>\n
If Target.Address = “$D$2” Then<\/p>\n
with this line:<\/p>\n
If Target.Address = “$D$2” Or Target.Address = “$D$3” Or Target.Address = “$D$4” Then<\/p>\n
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?<\/strong><\/p>\n
Ans:<\/strong> Replace the following line in the code to enable multiple drop-down selections for the full column:<\/p>\n
If Target.Address = “$C$2” Then<\/p>\n
with this line:<\/p>\n
If Target.Column = 3 Then<\/p>\n
Along the same lines, use the following line if you want this capability in columns C and D:<\/p>\n
If Target.Column = 3 or Target.Column = 4 Then<\/p>\n
Question 3: I need to make several drop-down menus in a succession. How can I go about this?<\/strong><\/p>\n
Ans:<\/strong> 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\u00a0row:<\/p>\n
If Target.Address = “$C$3” Then<\/p>\n
with this line:<\/p>\n
If Target.Row = 3\u00a0 Then<\/p>\n
Similarly, use the code below if you want this to function for more than one row, like the third and fourth rows:<\/p>\n
If Target.Row = 3\u00a0 or Target.Row = 4 Then<\/p>\n
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).<\/strong><\/p>\n
Ans:<\/strong> You must change the following line of VBA code in order to divide these with a separator other than a comma:<\/p>\n
Target.Value = Oldvalue & “, ” & Newvalue<\/p>\n
with this line of VBA code:<\/p>\n
Target.Value = Oldvalue & ” ” & Newvalue<\/p>\n
Similarly, you can use the following piece of code to replace a comma with another character, such as |:<\/p>\n
Target.Value = Oldvalue & “| ” & Newvalue<\/p>\n
Question 5: Is it possible to have each option in its own line within a single cell?<\/strong><\/p>\n
Ans:<\/strong> Certainly, you can. You must modify the following line of VBA code to achieve this:<\/p>\n
Target.Value = Oldvalue & “, ” & Newvalue<\/p>\n
with this line of code:<\/p>\n
Target.Value = Oldvalue & vbNewLine & Newvalue<\/p>\n
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.<\/p>\n
Question 6: Is it possible to use the numerous selection feature in a protected sheet?<\/strong><\/p>\n
Ans:<\/strong> Certainly, you can. You must take two actions in order to complete this:<\/p>\n
- \n
- After the DIM instruction, insert the following line into the code:<\/li>\n<\/ol>\n
Me.Protect UserInterfaceOnly:=True<\/p>\n
- \n
- 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.<\/li>\n<\/ol>\n
For ready-to-use Dashboard Templates:<\/strong><\/p>\n
- \n
- Financial Dashboards<\/a><\/li>\n
- Sales Dashboards<\/a><\/li>\n
- HR Dashboards<\/a><\/li>\n
- Data Visualization Charts<\/a><\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"
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 …<\/p>\n
- Sales Dashboards<\/a><\/li>\n
- Financial Dashboards<\/a><\/li>\n
- 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.<\/li>\n<\/ol>\n
- After the DIM instruction, insert the following line into the code:<\/li>\n<\/ol>\n
- Multiple Choices in a Drop-Down List are Supported by VBA (without repetition):<\/strong><\/li>\n<\/ul>\n
- Multiple Choices in a Drop-Down List are Supported by VBA (with repetition):<\/strong><\/li>\n<\/ul>\n
- Making the Drop Down List in Excel:<\/strong><\/li>\n<\/ul>\n
- Frequently Asked Questions (FAQs)<\/strong><\/a><\/li>\n<\/ol>\n