VBA & Macro Problems

Create a Dependent Excel Dropdown with Yes, No, and N/A Using VBA

Nimra Malik Oct 12, 2026 869 views

Question details

The user wants to create a conditional dropdown setup where selecting 'Yes' in a primary cell enables a specific dropdown list in an adjacent cell, while selecting 'No' automatically clears the validation and inputs 'N/A'.

How to Create a Dependent Dropdown List with Yes, No, and N/A in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Building dynamic data entry forms or surveys where subsequent cell options strictly depend on a previous Yes/No selection.
Observed behavior: Standard Excel Data Validation formulas cannot hardcode text like 'N/A' into a cell. A VBA Worksheet_Change event is required to monitor the source cells and automatically update or lock the dependent cells.

Before you start

Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled macros in your Trust Center settings so the VBA code can execute without interruption.

Solution 1Recommended

Use VBA Worksheet_Change Event to Control Dependent Cells

Apply a VBA script that monitors changes in your target column and dynamically applies Data Validation or inserts 'N/A' into the adjacent cell based on the user's input.

To achieve this automatic value insertion, standard Excel formulas are insufficient. You must add a VBA macro to the specific worksheet module. By utilizing the Worksheet_Change event, the code runs immediately and automatically whenever a value in your predefined source range is updated.

Open the VBA Editor

Right-click the specific worksheet tab at the bottom of your screen and select 'View Code'. This opens the Visual Basic for Applications editor directly inside the correct worksheet module.

Insert the Worksheet_Change Procedure

Paste a Private Sub Worksheet_Change(ByVal Target As Range) procedure into the blank code window. Use the Intersect function to ensure the macro only triggers when your specific range (e.g., AA3:AA511) is modified.

Configure the Automation Logic

Within the code, check the value of the Target cell. If the value is 'Yes', use Target.Offset(0, 1).Validation.Add to create your dependent dropdown list. If the value is 'No', use Target.Offset(0, 1).Validation.Delete to remove the dropdown and set Target.Offset(0, 1).Value = "N/A".

Toggle Application Events

Wrap your cell value changes with Application.EnableEvents = False at the start and Application.EnableEvents = True at the end. This prevents the macro from triggering itself in an infinite loop when it updates the adjacent cell.

Save as a Macro-Enabled Workbook

Close the VBA editor. Go to File > Save As, and choose 'Excel Macro-Enabled Workbook (*.xlsm)' from the file format dropdown to ensure your automation is preserved.

Use VBA Worksheet_Change Event to Control Dependent Cells

Code Placement is Critical: Worksheet_Change events must be placed in the specific Sheet module (e.g., Sheet1) visible in the VBA Project Explorer. If placed in a standard module (e.g., Module1), the automation will not trigger.

Advanced Spreadsheets with WPS Office

Easily Manage Data Validation and Macros with WPS Spreadsheet

WPS Spreadsheet offers robust support for Data Validation and VBA macros, allowing you to create complex, dependent dropdown lists seamlessly. Its highly compatible environment ensures your macro-enabled workbooks run flawlessly with familiar tools.

1. Open WPS Spreadsheet: Launch WPS Office and open your spreadsheet document.
2. Access Developer Tools: Navigate to the 'Developer' tab on the top ribbon and click 'Visual Basic Editor' to manage your macros.
3. Apply the Script: Right-click your active sheet name in the project explorer, paste your Worksheet_Change code, and save the file in a macro-enabled format.

Full compatibility with Microsoft Excel .xlsm and .xlsx formatsBuilt-in Visual Basic Editor for seamless macro creation and executionIntuitive Data Validation tools for managing dynamic listsLightweight application with fast processing speeds for heavy scripts

microsoft office alternative - wps office

Frequently Asked Questions

Why is my Worksheet_Change VBA code not triggering when I type Yes or No?

This usually happens for three reasons: macros are disabled in your security settings, the VBA code was mistakenly placed in a standard module instead of the specific worksheet object module, or Application.EnableEvents was left as False after a previous script error.

Can I achieve this with standard Data Validation formulas instead of VBA?

You can use an IF formula inside Data Validation to show a specific dropdown list when 'Yes' is selected and a blank list when 'No' is selected. However, standard formulas cannot automatically hardcode or push the text 'N/A' into a cell. Assigning a value to a cell based on another cell's input strictly requires VBA.

How do I apply this macro to an entire column instead of a specific range?

In your VBA code, change your Intersect target range to reference the entire column, such as Range("A:A") instead of Range("AA3:AA511"). Ensure you also add a check to ignore modifications to the header row (e.g., If Target.Row = 1 Then Exit Sub).

Excel & Data Problems