How to Control Navigation Between Unlocked Cells in Excel
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.

- 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.
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.
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.
Go to File > Options > Advanced. Under the 'Editing options' section, uncheck the box that says 'After pressing Enter, move selection', then click OK.
Press Alt + F11 on your keyboard to launch the Visual Basic for Applications (VBA) editor.
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.
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.

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. Open WPS Spreadsheet: Launch WPS Office and open your workbook or create a new form layout.
- 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. 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. 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.

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.




