VBA & Macro Problems

How to Use Excel Data Validation from Another Worksheet with VBA

Natalie Taylor Oct 16, 2026 869 views

Question details

The user wants to write a VBA macro to apply a data validation drop-down list to a specific cell (e.g., Sheet1 A2) using a populated range from a different worksheet (e.g., Sheet2 Column A) as the data source.

How to Apply Excel Data Validation from Another Worksheet Using VBA

Product: Spreadsheets
Device & OS: not provided
Scenario: Automating the creation of a dynamic drop-down list via a VBA script that pulls its source data from another worksheet in the same workbook.
Observed behavior: To successfully create the drop-down list, the VBA code needs to properly qualify references to both the target cell and the source worksheet ranges.

Before you start

Ensure that your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that the Developer tab is enabled to access the VBA Editor.

Solution 1Recommended

Use VBA with Fully Qualified Sheet References

Modify the VBA Validation object to explicitly reference both the target cell and the source worksheet range, preventing errors when running the macro.

When applying data validation across sheets in VBA, relying on the `Selection` object can cause run-time errors if the active sheet is not what you expect. Instead, fully qualify your target range and the source formula to ensure the macro executes flawlessly regardless of the currently active sheet.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications editor, then insert a new Module.

Define the Target Cell

Instead of using `With Selection.Validation`, explicitly define the target cell. Use code like `With Worksheets("Sheet1").Range("A2").Validation`.

Set Validation Parameters

Configure the validation parameters inside the With block. Add the source range by fully qualifying the sheet name in the formula string, for example: `.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=Sheet2!$A$1:$A$10"`.

Run the Macro

Save your code and run the macro. The drop-down list will be applied to cell A2 on Sheet1, referencing the data from Sheet2.

Use VBA with Fully Qualified Sheet References

Dynamic Data Reference: To make the source range dynamic via VBA, you can calculate the last populated row of Sheet2 first, then concatenate it into your Formula1 string.

Solution 2

Use a Dynamic Formula for the Validation Source

Use the INDEX and COUNTA functions within your VBA data validation formula to automatically capture all populated cells in the source column.

Seamlessly Execute VBA Macros

Use WPS Spreadsheet for VBA Macros and Data Validation

WPS Office Spreadsheet provides excellent support for VBA macros, allowing you to easily automate cross-worksheet data validation and build complex dynamic drop-down lists.

1. Enable the Developer Tab: Open WPS Spreadsheet, navigate to the Settings menu, and enable the Developer tools to make the macro environment visible.
2. Access the VBA Editor: Click the 'Developer' tab on the top ribbon and select 'Visual Basic', or use the shortcut Alt + F11 to launch the editor.
3. Write Your Macro Code: Insert a new module and paste your fully qualified validation code referencing the source and target sheets.
4. Run and Test: Run the macro to instantly generate your cross-sheet drop-down list directly in the target cell.

Fully compatible with Microsoft Excel VBA syntax and macro functionalityEasily reference data across different worksheetsBuilt-in Developer tab for quick script editingFree, lightweight, and fast performance

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA data validation code throw an Application-defined or Object-defined error?

This usually happens when you try to reference a range on a different worksheet without properly qualifying the sheet names, or if the source range formula string is incorrectly formatted. Ensure you use explicit sheet references within the string, such as `=Sheet2!$A$1:$A$10`, and ensure any old validation is cleared using `.Delete` before adding the new one.

Can I use a Named Range for cross-sheet data validation in VBA?

Yes. Defining a Named Range for your source data makes the VBA code cleaner and inherently supports cross-sheet references. You can simply set the Formula1 parameter to your named range, for example: `Formula1:="=MyNamedRange"`.

How do I make the drop-down list ignore blank cells using VBA?

In your VBA code, within the `With Validation` block, set the property `.IgnoreBlank = True`. Note that this prevents users from triggering an error if they leave the cell blank, but it does not remove empty cells from the drop-down list itself. To physically remove blanks from the list, you should use dynamic formulas like OFFSET or INDEX, or consolidate your source data.

Excel & Data Problems