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 XelPlus VBA course.
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.