Reset Dependent Drop-down in Excel

(with a tiny bit of VBA)

Learn how to reset a dependent drop-down list in Excel with a little bit of VBA.

The problem we get with dependent data validation lists is that when you change the value of the first data validation, the value for the dependent data validation remains until you activate the drop-down. This can be misleading.

In this tutorial, you will learn how to automatically reset the value of the dependent list to say “Please Select…” the moment the value in the first drop-down list changes.  We do this with Excel VBA, using the Change event procedure.

The default worksheet procedure is Selection Change event.  This triggers every time you change the active cell.  It’s more optimal in this case to use the worksheet Change event and make sure that it only run if the value of a specific cell is changed.  We don’t want to trigger the change procedure every time any cell value is changed.  The simple Excel macro shows how you can set it up from scratch.

Once you’re done, make sure you save the workbook as a macro-enabled workbook.

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.

Dependent Combo Box

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

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” Then

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

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

Excel Download Practice file

Unlock Excel VBA & Macros Course is here.

Save time. Achieve more.

Over 50 Excel macro examples for download & useful VBA codes you can use for your work.

Learn the WHY not just the HOW

LEARN MORE

6 Comments

  1. Renato Olivieri Ney December 16, 2018 at 1:07 am - Reply

    Leila, thank you very much for the valuable content that you make available on the web. It is being very helpful for me , I´m learning great things. I know the importance of getting educated not only for personal gains, but also to give for free and elevate mankind.
    Greetings from Brasil.

    • Leila Gharani December 20, 2018 at 9:06 am - Reply

      Thank you Renato! I’m glad you find the tutorials useful. Thank you for your support.

  2. Wendy Grijalva February 21, 2019 at 8:28 pm - Reply

    This has helped me so much but how can I do this for multiple rows of drop down menus. This only helps me out for my first row. Thank you!

  3. Shahfizan Ujang August 1, 2019 at 6:41 am - Reply

    Hi,
    For my case, how to apply for all change in column G. Next is for G4,G5……..Gn,

  4. Avinash Thomas August 1, 2019 at 9:09 am - Reply

    hi,
    what if i have 3 cells which are depended to each other, how can i do this to the third as well?

    • Ryan August 15, 2019 at 8:15 pm - Reply

      You can make another If statement.

      Here is an example I did with a third list:

      Private Sub Worksheet_Change(ByVal Target As Range)

      If Target.Address = “$C$2” Then

      Range(“C3”).Value = “”

      End If

      If Target.Address = “$C$3” Then

      Range(“C4”).Value = “”

      End If

      End Sub

Leave A Comment

Share This