EXCEL: Dynamic Dropdown List

Question

Hi! Friends:

Good day!

In a dynamic Dropdown List (DDL), the list displayed in the Secondary DDL is dependent on the value of the Main DDL that have been chosen.

I would like to know when the value in the Main DDL is re-selected, how to auto clear the old value that was displayed in the Secondary DDL previously?

Thank you.

Lee CC

Answer 1

Hi Lee,

What you intend to do can't be done directly with excel tabs or functions. Thus, you may need a VBA code;

-Press ALT + F11 to open the Visual Basic for Applications editor.

-In the worksheet's code module (where your DDLs are located), paste the following VBA code:

Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Range("A1")) Is Nothing Then Range("B1"). ClearContents End If End Sub

This VBA code is an event handler that triggers when there is a change in the worksheet. It checks if the change occurred in cell A1 (Main DDL cell) and if so, it clears the content of cell B1 (Secondary DDL cell).

-Close the VBA editor to save your changes.

Now, whenever you change the value in the Main DDL (cell A1), the content of the Secondary DDL (cell B1) will be automatically cleared, ensuring that the old value is removed.

I hope this helps.

Regards, Sola

Answer 2

Hi! Sola:

Thanks for the reply and the great helps.

I think the codes work only when the secondary DDL contains a blank cell.

Thanks again.

Lee CC