Creating a Dropdown List from a Generic List in Excel
In the dataset below, there are repeated values (Apple in B7 and B9) .
Create a dropdown list with Grapes, Orange, Apple, Mango and Apple
Steps:
- Click any cell ( D4, here) to display the dropdown list.
- Go to the tab Data.
- Select Data Validation in Data Tools.
In the Data Validation window:
- Select List in Allow.
- Drag B5:B9 to Source.
- Click OK.
This is the output.
In D4, a dropdown list holds the values (Grapes, Orange, Apple, Mango, Apple) retrieved from the generic list (B5:B9).
Method 1 – Embed VBA to Select Multiple Values from a Drop Down List in Excel (with repeated values)
There are repeated values in the dataset. To include all values in the dropdown list:
Steps:
- Press Alt + F11 or go to Developer -> Visual Basic to open Visual Basic Editor.
- Right-click the sheet name and select View Code.
- Enter the following code into the code window.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ValueA As String
Dim ValueB 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
ValueB = Target.Value
Application.Undo
ValueA = Target.Value
If ValueA = "" Then
Target.Value = ValueB
Else
Target.Value = ValueA & ", " & ValueB
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
- Don’t run the code, save it.
- Go back to the worksheet. If you click the created dropdown list in D4, you will be able to select multiple values.
VBA Code Breakdown
Dim ValueA As String
Dim ValueB As StringDefines the variables.
On Error GoTo ExitsubIf an error occurs, goes to label Exitsub.
If Target.Address = "$D$4" Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo ExitsubSets the destination as D4 which contains data validation. If there are no cells that contain data validation, it goes to label Exitsub.
Else: If Target.Value = "" Then GoTo Exitsub ElseIf there are no cells as the destination, it goes to label Exitsub. Otherwise, it executes the following lines.
Application.EnableEvents = FalseTurns off the Application Events, to prevent an infinite loop in the Worksheet_Change macro.
ValueB = Target.ValueDefines ValueB as the new value of the changed cell.
Application.Undoundoes the changed cell.
ValueA = Target.Valuedefines ValueA to be the old value of the changed cell.
If ValueA = "" Then
Target.Value = ValueBIf the old value is blank, it stores the new value as the destination.
Else
Target.Value = ValueA & ", " & ValueB
End If
End If
End IfOtherwise, it sets both old and the new values as the destination values by concatenating them with a comma (,). Closes all If statements.
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = TrueTurns on the Application Events.
Read More: Unique Values in a Drop Down List with VBA in Excel
Method 2 – Applying a VBA Macro to Select Multiple Values from a Drop Down List (without repeated values)
There are repeated values in the dataset. To create a dropdown list with all values, except the repeated ones:
Steps:
- Press Alt + F11 or go to Developer -> Visual Basic to open Visual Basic Editor.
- Right-click the sheet name and select View Code.
- Enter the following code into the code window.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ValueA As String
Dim ValueB 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
ValueB = Target.Value
Application.Undo
ValueA = Target.Value
If ValueA = "" Then
Target.Value = ValueB
Else
If InStr(1, ValueA, ValueB) = 0 Then
Target.Value = ValueA & ", " & ValueB
Else:
Target.Value = ValueA
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
- Don’t run the code, save it.
- Go back to the worksheet. If you click the dropdown list in D4, you will be able to select multiple values without repetitions.
VBA Code Breakdown
Dim ValueA As String
Dim ValueB As StringDefines the variables.
Application.EnableEvents = TrueTurns on the Application Events.
On Error GoTo ExitsubIf an error occurs, goes to label Exitsub.
If Target.Address = "$D$4" Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo ExitsubSets the destination as D4 which contains data validation. If there are no cells that contain data validation, it goes to label Exitsub.
Else: If Target.Value = "" Then GoTo Exitsub ElseIf there are no cells as the destination, it goes to label Exitsub. Otherwise, it executes the following lines.
Application.EnableEvents = FalseTurns off the Application Events to prevent an infinite loop in the Worksheet_Change macro.
ValueB = Target.ValueDefines ValueB as the new value of the changed cell.
Application.Undoundoes the changed cell.
ValueA = Target.Valuedefines the ValueA as the old value of the changed cell.
If ValueA = "" Then
Target.Value = ValueBIf the old value is blank, it stores the new value as the destination.
Else
If InStr(1, ValueA, ValueB) = 0 Then
Target.Value = ValueA & ", " & ValueBInStr function returns the position of the first occurrence of a substring in a string. If the output is 0, it sets both old and new values as destination values by concatenating them with a comma (,).
Else: Target.Value = ValueA
End If
End If
End If
End IfOtherwise, it sets the old value as the destination. Closes all If statements.
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = TrueTurns on the Application Events.
Download Workbook
Download the free practice Excel workbook .
Related Articles
- Excel VBA to Create Data Validation List from Array
- How to Create Dynamic Drop Down List Using VBA in Excel
- How to Make a Dynamic Data Validation List Using VBA in Excel
- Data Validation Drop Down List with VBA in Excel
- How to Use Named Range for Data Validation List with VBA in Excel
- How to Make Multiple Dependent Drop Down List with Excel VBA
