In this tutorial, we will see a simple VBA solution to the dynamic dependent drop-down list reset problem.
The Problem
Observe the dependent drop-down lists below.
We will select “Productivity Div” from the first drop-down (cell G4) and “WenCal” from the second drop-down (cell G6).
If we select “Game Div” from the first drop-down list, the value inside the dependent list does not reset. We still see the original App selection when the first drop-down was “Productivity Div”. “WenCal” belongs to the “Productivity” division.
When we select the second drop-down, we are presented with the correct list; we see “Fightrr”, “Kryptis”, etc. which are apps that belong to the “Game” division.
If we select the App “Fightrr” and switch to the “Utility” Division, we still see “Fightrr” until we click the second drop-down list and select an app from the updated list.
To learn how to create a dependent drop-down list, click the following link.
Dependent Data Validation in Excel
For a VBA-free approach to this problem that uses Form Control Combo Boxes, instead of Data Validation, click the following link.
The VBA Approach
This will be very simple. We want to place the phrase, “Please Select…“, in cell G6 the moment we change the value of the first drop-down list (cell G4). This will ensure that the displayed items for Division and App are not inconsistent.
Every time the value in cell G4 changes we want the content in cell G6 to reset. To get this to be automatic, we need to attach the code to an event.
Excel has many workbook and worksheet events, and many are covered in detail in my Excel VBA & Macros course.
Featured Course
Unlock Excel VBA & Excel Macros
See Online Excel Courses (including VBA) ► https://courses.xelplus.com/
In this example, we will use a simple event with a simple macro to get cell G6 to reset.
First off, we need to open the Visual Basic Editor. This can be opened using the shortcut key Alt-F11. Because we are going to be placing the code on the “List” sheet, we will right-mouse-click on the “List” sheet tab and select View Code.
This brings up the VBA Editor and ensures we are on the correct sheet.
You need to ensure that you are placing the VBA event code inside the correct sheet. The sheet holding the drop-down lists is named “List”, so double-check that the “List” sheet is selected from the Project Explorer on the left side of the screen.
From the left-most drop-down in the code window, select Worksheet.
By default, we are given “shell code” for the Selection Change event. Selection Change runs each time a cell or range selection is made.
Observe that Selection Change has a variable called Target as Range.
We will use that for our demonstration. We will use a message box to display the address of the selected cell (Target).
When we select cell F11 we see the following message box.
Notice that the message box macro is running every time a cell is selected.
We could run the macro to reset the dependent drop-down list every time any cell is selected, but that would be undesirable. We only want the second list to reset when the value in the first list changes. An event better suited for this behavior is called the Change event.
From the VBA Editor, select Change event from the drop-down.
Copy and paste the action from the Selection Change event into the Change event and delete the original Selection Change event.
Running the Updated Code
If we select a cell, the macro does not run. However, the moment we alter the content of a cell the macro runs.
Having the code run every time any cell value is changed is also undesirable. We only want the code to run if the value of cell G4 changes. We will restrict code execution by using an IF Statement.
We will check to see if the value of the target cell (cell G4) has changed using the below test.
If Target.Address = “$G$4” ThenIf the contents of cell G4 have changed, we want to reset the value of the dependent list (cell G6), so we will use the following code.
Range(“G6”).Value = “Please Select…”Running the Final Version of the Code
The final version of the code appears as follows:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$4" Then
Range("G6").Value = "Please Select..."
End If
End SubChange the selection in the first drop-down list (cell G4). Cell G6 says “Please Select…”.
Now select an App from the second drop-down list.
Change the selection in the first drop-down list to a different Division. Notice the “Please Select…” message has replaced the previously selected App in the second drop-down list.
Advantage of the Final Method
This macro does not run if any other cells are selected or if their content is changed. The macro only runs if the value of cell G4 changes. This behavior ensures whatever App name appears in the second list relates to the Division seen in the first list.
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
