VBA & Macro Problems

How to Navigate to a Row Using an Excel Drop-Down List with VBA

Maira Mehtab Sep 22, 2026 869 views

Question details

The user wants to create a drop-down list in the first row that displays titles from column A, allowing automatic navigation to the corresponding row when a title is selected. Additionally, they are unable to find the 'View Code' option when right-clicking the worksheet tab.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Creating an interactive drop-down list to jump to specific rows in a large dataset.
Observed behavior: The user needs a VBA solution to navigate to a row based on a drop-down selection, but is hindered by the missing 'View Code' option in their current Excel version.

Before you start

Ensure you are using the desktop version of Excel, as VBA macros and the 'View Code' feature are not supported in Excel for the web.

Solution 1Recommended

Use Data Validation and the Worksheet_Change VBA Event

This method combines a dynamic drop-down list with a VBA macro to automatically jump to the selected row.

To achieve automatic navigation, you first need to create a data validation list to hold your titles, then apply a VBA script to trigger the navigation event. Note that the 'View Code' feature is exclusive to the desktop version of Excel.

Create the drop-down source

Select cell C1 (or your preferred cell in the first row), navigate to the Data tab on the ribbon, and click 'Data Validation'.

Set the validation criteria

In the Data Validation dialog, choose 'List' from the Allow drop-down. Enter a formula like =OFFSET(A2,0,0,COUNTA(A:A)-1,1) in the Source box to dynamically display titles from Column A, then click OK.

Open the VBA Editor

Right-click the worksheet tab at the bottom of your screen and select 'View Code'. If this option is missing, verify that you are not using Excel for the web.

Add the Worksheet_Change event

In the VBA code window, set the left drop-down to 'Worksheet' and the right drop-down to 'Change'. Write a VBA script within this event procedure to search Column A for the target value selected in C1 and use the Select or Activate method to jump to that specific row.

Save your workbook

Go to File > Save As and ensure you save the file as an Excel Macro-Enabled Workbook (.xlsm) to prevent your VBA code from being deleted.

Missing 'View Code' Option: If you cannot find 'View Code' when right-clicking the worksheet tab, you are likely using Excel for the web. You must open the file in the desktop application to use VBA and event procedures.

Efficient Spreadsheet Automation

Use Macros and Drop-Downs Easily in WPS Office

WPS Office Spreadsheet provides full support for VBA macros and data validation, allowing you to create interactive, automated worksheets just like Microsoft Excel. You can seamlessly run your .xlsm files and write event procedures.

1. Open your workbook: Launch WPS Spreadsheet and open your existing dataset.
2. Create the Drop-Down: Go to the Data tab and select Validation to create your dynamic drop-down list using the OFFSET formula.
3. Access the VBA Editor: Navigate to the Developer tab (or right-click the sheet tab) and select 'View Code' to open the VBA editor.
4. Insert Macro and Save: Paste your Worksheet_Change macro code to handle the navigation and save the document as a Macro-Enabled Workbook (.xlsm).

Fully compatible with Microsoft Excel (.xlsx, .xlsm, .xls) formats.Built-in VBA editor to easily access 'View Code' and write automated macros.Free and lightweight alternative for complex data management.Familiar user interface with no steep learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why is 'View Code' missing when I right-click the Excel sheet tab?

The 'View Code' option is only available in the desktop versions of Excel that support VBA. If you are using Excel for the web (Excel Online) or a mobile app, this feature is not supported.

Can I use a formula to navigate to a row instead of VBA?

Formulas like OFFSET or HYPERLINK can create dynamic lists or clickable links to cells, but they cannot automatically trigger navigation to a row simply by selecting an item from a drop-down list. That specific automated action requires a VBA Worksheet_Change event.

Why didn't my VBA code save when I closed the document?

If you save a workbook containing VBA macros as a standard Excel Workbook (.xlsx), the macro code will be stripped out and lost. You must always save the file as an Excel Macro-Enabled Workbook (.xlsm) to retain any scripts.

Excel & Data Problems