VBA & Macro Problems

How to Link an Excel Shape to a Worksheet by Sheet Number

Anum MushtaqAnum Mushtaq Nov 10, 2026 870 views

Question details

The user wants to create a clickable shape that navigates to another worksheet using its numerical index position rather than its specific name.

How to Link an Excel Shape to a Worksheet by Sheet Number using VBA
Product
Microsoft Excel / WPS Spreadsheet
Device & OS
not provided
Scenario
Creating interactive spreadsheet navigation where worksheet names might change, but their sequential positions in the workbook remain relevant.
Observed behavior
The standard hyperlink dialog only allows linking to existing worksheet names, lacking an option to navigate via the worksheet's dynamic index number.
Before you start

Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled Developer tools and macros in your spreadsheet application.

Solution 1Recommended

Assign a VBA Macro to Activate Worksheet by Index

Create a short VBA script to activate a sheet by its numerical order and attach this script to your shape to act as a custom hyperlink.

VBA (Visual Basic for Applications) allows you to reference sheets by their physical position from left to right. By assigning a simple macro to your shape, you bypass the limitations of the standard hyperlink tool.

1
Open the VBA Editor

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

2
Insert a New Module

In the top menu, click 'Insert' and select 'Module' to create a blank script window for your code.

3
Write the Macro Code

Type the following code into the module window: Sub GoToSheetByIndex() ActiveWorkbook.Sheets(3).Activate End Sub (Replace '3' with the index number of the sheet you wish to link to).

4
Assign the Macro to the Shape

Return to your main worksheet. Right-click the shape you want to act as the link, select 'Assign Macro', choose 'GoToSheetByIndex' from the list, and click 'OK'.

Assign a VBA Macro to Activate Worksheet by Index
Index Number Caution: The index number represents the physical position of the sheet tab. If you drag and reorder the sheet tabs, the target of your macro will change dynamically to whatever sheet now occupies that position.
Advanced Macros in WPS

Use WPS Spreadsheet to Manage Macros and Shapes

WPS Spreadsheet provides excellent support for VBA macros and advanced developer tools, allowing you to create custom navigation buttons and automate tasks seamlessly.

  1. 1. Enable Developer Tools: Open WPS Spreadsheet, navigate to the 'Developer' tab, and ensure Macros are enabled in your security settings.
  2. 2. Insert a Shape: Go to the 'Insert' tab, click on 'Shapes', and draw a button on your current worksheet.
  3. 3. Add the VBA Script: Click 'Macros' > 'Create' or press Alt + F11, then input the ActiveWorkbook.Sheets(n).Activate code.
  4. 4. Assign the Macro: Right-click your newly inserted shape, select 'Assign Macro', choose your written script, and confirm to activate your custom link.
Fully supports VBA macros and scripting syntaxHighly compatible with Microsoft Excel (.xlsm) macro formatsBuilt-in shape library for creating sleek, modern navigation buttonsLightweight application that loads heavy VBA workbooks quickly
microsoft office alternative - wps office

Frequently Asked Questions

Why does my macro link open the wrong sheet after moving tabs?

The Sheets(n) VBA method references the worksheet by its physical position (from left to right) at the bottom of the window, not by its fixed creation ID. If you move a tab, its index number changes.

Can I use this macro to navigate to a hidden worksheet?

No, you cannot activate a hidden worksheet directly. You must use VBA to unhide the sheet first (e.g., Sheets(n).Visible = True) before calling the Activate method.

How do I save a workbook that contains this shape macro?

You must save the file in a macro-enabled format. When saving, select 'Excel Macro-Enabled Workbook (*.xlsm)' from the file type dropdown menu to ensure your VBA code is preserved.

Can I pass the sheet number dynamically instead of hardcoding it?

Yes, you can retrieve the sheet index number dynamically by reading a value from a specific cell. For example, replacing the hardcoded number with Range("A1").Value will activate whichever sheet number is typed into cell A1.