VBA & Macro Problems

How to Control Navigation Between Unlocked Cells in Excel

Huda QurayshiHuda Qurayshi Nov 10, 2026 869 views

Question details

The user wants to configure a custom sequence for navigating between specific unlocked cells in a protected worksheet, overriding the default row-by-row or column-by-column movement.

How to Control Navigation Between Unlocked Cells in Excel
Product
Excel
Device & OS
not provided
Scenario
Creating a protected data entry form where users must fill out specific fields in a predetermined, non-linear order.
Observed behavior
By default, Excel automatically moves the cursor to the adjacent cell in a row or column when pressing Enter, which disrupts the intended workflow for customized forms.
Before you start

Ensure you have unlocked the target cells by right-clicking them, selecting 'Format Cells', and unchecking 'Locked' under the Protection tab before applying workbook protection and macros.

Solution 1Recommended

Use a VBA Macro to Define Custom Cell Navigation Order

You can override default cell movement by disabling Excel's automatic Enter key behavior and applying a Worksheet_SelectionChange macro to handle the navigation flow.

To make this method work seamlessly, you must first stop Excel from automatically moving the cursor down or right when the Enter key is pressed. The VBA script will take over the responsibility of calculating and selecting the next cell in your defined sequence.

1
Disable Default Enter Key Movement

Go to File > Options > Advanced. Under the 'Editing options' section, uncheck the box that says 'After pressing Enter, move selection', then click OK.

2
Open the VBA Editor

Press Alt + F11 on your keyboard to launch the Visual Basic for Applications (VBA) editor.

3
Insert the Macro Code

In the Project Explorer panel on the left, double-click the specific worksheet where you want the navigation applied (e.g., Sheet1). Paste the Worksheet_SelectionChange macro code into the code window.

4
Customize the Cell Order

Locate the 'cellOrder = Array("B2", "D2", "B4", "D4")' line in the pasted VBA script. Change these cell references to match the exact sequence you want the user to follow. Close the VBA editor to test your form.

Use a VBA Macro to Define Custom Cell Navigation Order
Save as Macro-Enabled File: To retain this custom functionality, you must save your file as an Excel Macro-Enabled Workbook (.xlsm). Saving it as a standard .xlsx file will strip the VBA code upon closing.
Advanced Spreadsheet Management

Create Custom Data Entry Forms with WPS Spreadsheet

WPS Office Spreadsheet provides excellent native support for VBA macros and worksheet protection. You can easily build highly customized data entry forms and control cell navigation flows just as you would in Microsoft Excel.

  1. 1. Open WPS Spreadsheet: Launch WPS Office and open your workbook or create a new form layout.
  2. 2. Access the Developer Tools: Navigate to the 'Developer' tab on the top ribbon and click on 'VB Editor' to access the macro coding environment.
  3. 3. Apply the Navigation Macro: Double-click your target sheet on the left panel and paste your Worksheet_SelectionChange script to set the cell array order.
  4. 4. Protect Your Form: Go to the 'Review' tab, click 'Protect Sheet', and ensure users can only select unlocked cells to finalize your form's navigation setup.
Fully compatible with Microsoft Excel macro formats including .xlsm and .xlsb.Seamlessly run existing VBA macros for custom cell navigation and form logic.Lightweight design with a familiar, easy-to-navigate tabbed interface.Completely free built-in developer tools to edit, debug, and run VBA code.
microsoft office alternative - wps office

Frequently Asked Questions

Why does the macro stop working when I protect the worksheet?

If your VBA script attempts to select or modify cells that are still locked, it will trigger an error on a protected sheet. Ensure all cells listed in your navigation array are set to 'Unlocked' (via Format Cells > Protection) before applying worksheet protection.

Can I use the Tab key instead of Enter for this macro navigation?

Yes. The provided VBA macro uses a SelectionChange event, meaning it evaluates the active cell whenever the selection changes. This triggers appropriately whether you press Enter, hit the Tab key, or manually click on a cell with your mouse.

How do I ensure the cursor loops back to the first cell at the end?

The suggested macro includes a conditional check (If i < UBound). When the code reaches the last cell in the array, the 'Else' statement executes, defining the 'nextCell' as index 0 (the first cell in the array), creating a continuous navigation loop automatically.