Dropdown lists are a very useful feature to perform various Excel related tasks. Implementing VBA is the most effective, quickest, and safest method to run any operation in Excel. In this article, we will show you 2 effective methods for how to select the value from drop down list in Excel with the VBA macro.
Creating a Dropdown List from a Generic List in Excel
Before diving into the coding section, let’s learn a very simple way to create a dropdown list from a generic list in Excel. So that later we can utilize the dropdown list that we created as the example of this article.
Following is the generic list that we have in our Excel worksheet. There are repetitive values (e.g., Apple in Cell B7 and B9) in the list.
We will see how to make a dropdown list consisting of the values (e.g., Grapes, Orange, Apple, Mango, Apple) from the generic list.
Steps:
- At first, click on any cell (Cell D4 in our case) where you want to store the dropdown list.
- Then, click on the tab Data.
- After that, select Data Validation from the Data Tools group of the ribbon.
- A Data Validation pop-up box will appear. From there,
- Select List in the Allow criteria.
- In the Source criteria, drag the range (B5:B9 in our case) that has the values for the dropdown list.
- Later, click OK.
Look at the following image.
In Cell D4, there is a dropdown list created which holds the values (e.g., Grapes, Orange, Apple, Mango, Apple) retrieved from the generic list (range B5:B9).
2 Methods with VBA to Select Value from Drop Down List in Excel
In this section, you will learn how to select multiple values with both repetitive and non-repetitive values from a dropdown list in Excel with VBA.
1. Embed VBA to Select Multiple Values from Drop Down List in Excel (with repetitive values)
We have repetitive values in our dataset. If you want your dropdown list to catch all the values no matter if the value is double or not, then follow the steps below.
Steps:
- In the beginning, press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.
- Next, right-click on the appropriate sheet name and select View Code from the option list that appeared.
- Then, copy the following code and paste it 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 this code, save it.
- Now go back to the worksheet of interest. If you click on the created dropdown list in Cell D4, you will see you can now select multiple values from the dropdown (look at the following gif).
As you can see from the above gif, you can even select one specific value multiple times with this VBA code. The macro code that we provided in this section will let the dropdown list select all types of values.
VBA Code Explanation
Dim ValueA As String
Dim ValueB As StringDefining variable names.
On Error GoTo ExitsubIf an error occurs, go to the label Exitsub.
If Target.Address = "$D$4" Then
   If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
   GoTo ExitsubSets the destination as cell D4 which contains data validation. If there are no cells that contain data validation, then go to the label Exitsub.
Else: If Target.Value = "" Then GoTo Exitsub ElseIf there are no cells as the destination, then go to the label Exitsub. Otherwise, execute the following lines.
Application.EnableEvents = FalseTurning off the Application Events so that the Worksheet_Change macro can be prevented from firing; Otherwise, it can cause a potential infinite loop.
ValueB = Target.ValueDefining the ValueB to be the new value of the changed cell.
Application.UndoTo undo the changed cell.
ValueA = Target.ValueBy undoing the change, we can now define the ValueA to be the old value of the changed cell.
If ValueA = "" Then
Target.Value = ValueBIf the old value is blank, then store the new value as the destination.
Else
Target.Value = ValueA & ", " & ValueB
  End If
 End If
End IfOtherwise, set both the old and the new values are the destination values by concatenating them with a comma (,). Closing all If statements.
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = TrueTurning back on the Application Events.
Read More: Unique Values in a Drop Down List with VBA in Excel
2. Apply VBA Macro to Select Multiple Values from Drop Down List (without repetitive values)
We have repetitive values in our dataset. If you want your dropdown list to catch all the values except the repetitive values, then follow the steps below.
Steps:
- As shown before, open Visual Basic Editor from the Developer tab.
- Then, go to the code window from the View Code option appeared by right-clicking the worksheet of interest.
- Then, copy the following code and paste it into the code window of the specified worksheet.
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 this code, save it.
- Now go back to the worksheet of interest. If you click on the created dropdown list in Cell D4, you will see you can now select multiple values from the dropdown (look at the following gif).
As you can see from the above gif, you cannot select one specific value multiple times with this VBA code. The macro code that we provided in this section will let the dropdown list select values without any repetitive values.
VBA Code Explanation
Dim ValueA As String
Dim ValueB As StringDefining variable names.
Application.EnableEvents = TrueTurning back on the Application Events.
On Error GoTo ExitsubIf an error occurs, go to the label Exitsub.
If Target.Address = "$D$4" Then
   If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
   GoTo ExitsubSets the destination as cell D4 which contains data validation. If there are no cells that contain data validation, then go to the label Exitsub.
Else: If Target.Value = "" Then GoTo Exitsub ElseIf there are no cells as the destination, then go to the label Exitsub. Otherwise, execute the following lines.
Application.EnableEvents = FalseTurning off the Application Events so that the Worksheet_Change macro can be prevented from firing that can cause a potential infinite loop.
ValueB = Target.ValueDefining the ValueB to be the new value of the changed cell.
Application.UndoTo undo the changed cell.
ValueA = Target.ValueBy undoing the change, we can now define the ValueA to be the old value of the changed cell.
If ValueA = "" Then
Target.Value = ValueBIf the old value is blank, then store 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 then set both the old and the new values are the destination values by concatenating them with a comma (,).
Else: Target.Value = ValueA
  End If
 End If
 End If
End IfOtherwise, set the old value as the destination. Closing all If statements.
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = TrueTurning back on the Application Events.
Read More: Default Value in Data Validation List with Excel VBA
Download Workbook
You can download the free practice Excel workbook from here.
Conclusion
To conclude, this article showed you 2 effective methods on how to select the value from drop down list in Excel with the VBA macro. I hope this article has been very beneficial to you. Feel free to ask any questions regarding the topic.
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
