VBA & Macro Problems

How to Set a Custom Range Tab Order with VBA Macros in Excel

Aamir Naveed AkramAamir Naveed Akram Oct 28, 2026 872 views

Question details

The user needs a way to navigate through noncontiguous worksheet cells in a specific, custom sequence (e.g., A6:C6, then jumping to L6:N6, moving row by row) without using form controls.

How to Create a Custom Range Tab Order in Excel Using VBA
Product
Microsoft Excel
Device & OS
not provided
Scenario
Performing repetitive data entry across specific, disconnected columns on a worksheet where standard left-to-right tab navigation is inefficient.
Observed behavior
Standard form-control tab orders do not apply to ordinary worksheet cells, requiring a custom VBA macro to override the default cursor movement behavior.
Before you start

Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that Developer mode is activated in your ribbon to access the VBA editor.

Solution 1Recommended

Use a Worksheet Event Macro to Define Custom Cell Navigation

Implement a VBA script that detects the current cell selection and automatically shifts the active cell to the next required column or row in your custom sequence.

Since standard Tab Order settings are limited to UserForms and ActiveX controls, ordinary worksheet cells require event-driven VBA code to customize the navigation path.

By utilizing the Worksheet_SelectionChange or Worksheet_Change event, you can program Excel to monitor the active cell and redirect the cursor exactly where you need it—such as jumping from C6 to L6, and N6 to A7.

1
Open the VBA Editor

Press the 'Alt + F11' keys simultaneously in your Excel workbook to open the Visual Basic for Applications (VBA) editor.

2
Access the Specific Worksheet Module

In the Project Explorer pane on the left side, double-click the specific worksheet (e.g., 'Sheet1') where you want the custom tab order to be applied.

3
Insert the Navigation Script

Write a VBA script utilizing a loop or the Worksheet_Change event. For the required pattern, set conditions so that when a cell in column C is completed, the selection offsets to column L of the same row; and when column N is completed, it offsets to column A of the next row.

4
Test the Custom Tab Order

Close the VBA editor and return to your worksheet. Click on cell A6, enter your data, and press Enter or Tab to verify that the cursor moves sequentially through A6, B6, C6, jumps to L6, M6, N6, and wraps correctly to A7.

Use a Worksheet Event Macro to Define Custom Cell Navigation
Macro Security Settings: Make sure that macros are enabled in your Excel Trust Center settings, otherwise the custom navigation event trigger will not execute.
Run Macros Seamlessly in WPS

Automate Data Entry with VBA in WPS Spreadsheet

WPS Office Spreadsheet provides robust support for VBA macros, allowing you to easily write event-driven scripts to customize tab orders and automate repetitive tasks just like in Microsoft Excel.

  1. 1. Download and Open WPS Spreadsheet: Launch WPS Office and open your existing spreadsheet or create a new one.
  2. 2. Enable the Developer Tab: Navigate to the ribbon menus. If not visible, enable the 'Developer' tab from the settings to access macro tools.
  3. 3. Open the VBA Editor: Click 'Visual Basic' or 'Macro' in the Developer tab to open the built-in VBA editor.
  4. 4. Run Your Custom Scripts: Paste your custom tab order VBA code, save the file as a Macro-Enabled Workbook, and instantly streamline your data entry.
Fully compatible with Microsoft Excel macro-enabled files (.xlsm)Built-in Developer tools and comprehensive VBA editorFree, lightweight, and fast-loading spreadsheet alternativeSeamless migration of complex spreadsheet logic and formulas
microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't the standard Tab Order setting work on my Excel worksheet?

The standard Tab Order feature in Excel is exclusively designed for UserForms and ActiveX form controls. It does not apply to regular grid cells on a worksheet. To control cursor movement between ordinary worksheet cells, you must use either VBA programming or sheet protection.

How do I loop through specific noncontiguous columns using VBA?

You can use a 'For...Next' loop for your rows (e.g., 'For i = 6 To 36') and nest column loops or specify exact column offsets within that row loop. By instructing the code to process columns 1 to 3 (A:C) and then columns 12 to 14 (L:N), you create a customized noncontiguous processing sequence.

Does WPS Office support VBA macros?

Yes, WPS Office supports VBA macros. You can open, edit, and run .xlsm files and VBA scripts seamlessly in WPS Spreadsheet using its built-in Developer tools and Visual Basic editor.