VBA & Macro Problems

How to Clear Excel Data Validation Values on Selection Change

Maira Mehtab Sep 27, 2026 870 views

Question details

The user needs to automatically clear the selected value in a dependent data validation drop-down when the primary controlling cell is updated, and needs help fixing a VBA error caused by duplicate macros.

Product: Excel
Device & OS: not provided
Scenario: Managing dependent drop-down lists where updating a parent category requires the child category cell to automatically reset its value.
Observed behavior: The dependent cell retains its previously selected value after the controlling cell is changed. Additionally, adding a second Worksheet_Change event to fix this triggers an 'Ambiguous Name detected' VBA error.

Before you start

Ensure the Developer tab is enabled in your Excel ribbon and take note of the exact cell references for your controlling and dependent data validation lists before editing the VBA code.

Solution 1Recommended

Combine Multiple Worksheet_Change Events into a Single Procedure

Resolves the 'Ambiguous Name detected' error by merging the new clearing logic into your existing Worksheet_Change event.

Excel VBA only allows one Worksheet_Change event per sheet module. If you paste a second Worksheet_Change procedure to clear validation values, VBA cannot determine which one to run.

Open the VBA Editor

Press Alt + F11 to open the VBA Editor, then double-click the specific worksheet module in the Project Explorer containing your existing code.

Remove the Duplicate Header

Delete the newly added Private Sub Worksheet_Change(ByVal Target As Range) header and its closing End Sub that are causing the ambiguous name error.

Merge the Logic

Insert your new If Not Intersect(Target, Range("W2")) Is Nothing Then condition inside the remaining, original Worksheet_Change block.

Add the Clear Action

Within your newly added condition, insert the line Range("X3:Y3").ClearContents to automatically clear the dependent validation cells when the controlling cell changes.

Disable Events Temporarily: To prevent an infinite loop where clearing the cell triggers the Worksheet_Change macro again, wrap the clear command with Application.EnableEvents = False before it, and Application.EnableEvents = True immediately after.

Solution 2

Configure Dependent Drop-down Lists Using INDIRECT

Sets up the data validation source so that the options in the secondary list update dynamically based on the primary selection.

Create Dynamic Drop-downs and Macros in WPS Spreadsheet

WPS Spreadsheet fully supports VBA macros and advanced data validation formulas like INDIRECT. You can easily manage dependent drop-down lists and complex Worksheet_Change events within a highly intuitive and familiar interface.

1. Access the Developer Tab: Open your workbook in WPS Spreadsheet and navigate to the Developer tab to launch the VBA Editor.
2. Set Up Data Validation: Define your named ranges via the Formulas tab and apply the =INDIRECT() rule in the Data Validation menu.
3. Apply the Macro: Insert your combined Worksheet_Change macro directly into the sheet module to automate clearing dependent cells without ambiguous name errors.

Full compatibility with Microsoft Excel .xlsm and .xlsx formatsBuilt-in VBA editor for seamlessly managing event proceduresNative support for advanced data validation and the INDIRECT functionLightweight application with a familiar, tabbed user interface

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Excel macro show an 'Ambiguous Name detected' error?

This error occurs when you have multiple procedures with the exact same name (such as two Worksheet_Change macros) within the same sheet module. Excel does not know which one to execute. To fix this, you must merge the internal logic of both macros into a single procedure block.

Does the INDIRECT function clear the old data validation value automatically?

No, the =INDIRECT() function only updates the background list of available drop-down options based on the controlling cell. The previously selected text will remain visibly sitting in the cell until you manually delete it or use a VBA event macro to automatically clear it.

How do I stop a Worksheet_Change macro from triggering itself continuously?

When a macro modifies a cell's contents (like using ClearContents), it triggers another Worksheet_Change event, causing an infinite loop. Prevent this by adding the line Application.EnableEvents = False before the clearing action and Application.EnableEvents = True immediately after.

Excel & Data Problems