VBA to Select Value from Drop Down List in Excel (2 Methods)

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.


Download Workbook

You can download the free practice Excel workbook from here.


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.

Creating dropdown list for excel vba select value from drop down list

  • 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.

Created dropdown list for excel vba select value from drop down list

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

VBA to select all value from drop down list in Excel

  • 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).

Result of VBA to select all value from drop down list in Excel

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 String

Defining variable names.

On Error GoTo Exitsub

If an error occurs, go to the label Exitsub.

If Target.Address = "$D$4" Then
    If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub

Sets 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 Else

If there are no cells as the destination, then go to the label Exitsub. Otherwise, execute the following lines.

Application.EnableEvents = False

Turning 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.Value

Defining the ValueB to be the new value of the changed cell.

Application.Undo

To undo the changed cell.

ValueA = Target.Value

By undoing the change, we can now define the ValueA to be the old value of the changed cell.

If ValueA = "" Then
    Target.Value = ValueB

If the old value is blank, then store the new value as the destination.

Else
    Target.Value = ValueA & ", " & ValueB
  End If
 End If
End If

Otherwise, 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 = True

Turning back on the Application Events.

Read More: How to Create Drop Down List in Excel with Multiple Selections


Similar Readings:


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

VBA to select all value without repetitive values from drop down list in Excel

  • 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).

Result of VBA to select all value without repetitive values from drop down list in Excel

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 String

Defining variable names.

Application.EnableEvents = True

Turning back on the Application Events.

On Error GoTo Exitsub

If an error occurs, go to the label Exitsub.

If Target.Address = "$D$4" Then
    If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub

Sets 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 Else

If there are no cells as the destination, then go to the label Exitsub. Otherwise, execute the following lines.

Application.EnableEvents = False

Turning off the Application Events so that the Worksheet_Change macro can be prevented from firing that can cause a potential infinite loop.

ValueB = Target.Value

Defining the ValueB to be the new value of the changed cell.

Application.Undo

To undo the changed cell.

ValueA = Target.Value

By undoing the change, we can now define the ValueA to be the old value of the changed cell.

If ValueA = "" Then
    Target.Value = ValueB

If the old value is blank, then store the new value as the destination.

Else
    If InStr(1, ValueA, ValueB) = 0 Then
        Target.Value = ValueA & ", " & ValueB

InStr 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 If

Otherwise, set the old value as the destination. Closing all If statements.

Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True

Turning back on the Application Events.

Read More: Excel Drop Down List Depending on Selection


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

Sanjida Ahmed

Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that haven’t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo