Keep Excel Dashboard Links Working When Worksheets Are Hidden in WPS Spreadsheets
Question details
The user needs dashboard hyperlinks to hidden Excel worksheets to keep working by using VBA to temporarily unhide and activate the target worksheet.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A user has a dashboard workbook with hyperlinks that point to hidden worksheets and wants navigation to work without keeping those sheets visible.
- Observed behavior
- Clicking a hyperlink to a hidden worksheet cannot be followed interactively because the hidden sheet remains inaccessible until it is made visible.
Save the workbook as an Excel Macro-Enabled Workbook (.xlsm), enable macros, and confirm that the destination sheet name exactly matches the hyperlink reference, such as #'Emirates'!A1.
Use a Worksheet_FollowHyperlink macro to open hidden worksheets
Best for dashboards that already use normal hyperlinks to hidden sheets; the macro intercepts the click, temporarily unhides the target, and activates it.
Standard hyperlinks such as #'Emirates'!A1 cannot follow a hidden worksheet because the destination sheet must be visible before it can be activated. A sheet code name such as Sheet1 does not change this behavior.
The Worksheet_FollowHyperlink event runs when a hyperlink on that specific dashboard worksheet is clicked, so the VBA runs before the destination is opened.
Save the workbook as an Excel Macro-Enabled Workbook (.xlsm). In WPS Spreadsheets, go to Developer > Macro Security and set it to enable or prompt so the VBA code can run.
Press Alt+F11 or click Developer > Visual Basic. In Project Explorer, double-click the worksheet that contains the dashboard hyperlinks, for example Sheet1 (Dashboard).
Paste the following code into the worksheet module, then adjust the cell reference to match your dashboard target cell: Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) Dim ws As Worksheet Dim sheetName As String sheetName = Replace(Replace(Split(Target.SubAddress, "!")(0), "'", ""), "#", "") On Error Resume Next Set ws = ThisWorkbook.Worksheets(sheetName) On Error GoTo 0 If ws Is Nothing Then Exit Sub ws.Visible = xlSheetVisible ws.Activate Range("A1").Select End Sub
Press Ctrl+S in the VBA editor, close it, and return to the dashboard. Click a hyperlink such as #'Emirates'!A1. WPS Spreadsheets should unhide the target worksheet, activate it, and select the target cell.
Use a dedicated GoToHiddenSheet macro for each dashboard button
Use this if you prefer explicit navigation buttons instead of relying on hyperlink events.
Fix hidden-sheet dashboard links in WPS Spreadsheets with VBA
Open your Excel dashboard in WPS Spreadsheets, enable macros, and add a short VBA event macro that momentarily unhides the target worksheet and selects the linked cell. This keeps the familiar hyperlink behavior while keeping sheets hidden the rest of the time.
- 1. Download and install WPS Office: Download WPS Office from the official WPS site and install WPS Spreadsheets on your computer.
- 2. Open the dashboard workbook: Open your existing .xlsm workbook in WPS Spreadsheets and keep the dashboard worksheet visible.
- 3. Enable macro security: Click Developer > Macro Security, choose Enable all macros or prompt, and reopen the workbook if prompted.
- 4. Add the FollowHyperlink macro: Press Alt+F11, open the dashboard worksheet module, and paste the Worksheet_FollowHyperlink code that unhides and activates the target sheet.
- 5. Save and test: Save the workbook as an Excel Macro-Enabled Workbook (.xlsm), then click a dashboard link to confirm the hidden worksheet opens correctly.
Frequently Asked Questions
Why do hyperlinks to hidden worksheets not work in WPS Spreadsheets or Excel?
Neither WPS Spreadsheets nor Excel can follow a hyperlink to a hidden worksheet interactively because the destination sheet is not visible. Use a VBA macro to temporarily unhide the sheet and activate it.
Does using a sheet code name in the hyperlink fix hidden worksheet links?
No. A sheet code name such as Sheet1 is for VBA only and does not make a hidden worksheet accessible through a hyperlink. The hyperlink should use the visible worksheet name, for example #'Emirates'!A1.
Do I need to save the workbook as .xlsm for the VBA solution?
Yes. VBA macros are stored in macro-enabled workbooks, so save as .xlsm and enable macros. An .xlsx file can remove or disable the macro.
Where should I put the Worksheet_FollowHyperlink code?
Put it in the worksheet module of the dashboard sheet that contains the hyperlinks. Do not place it in a standard module or the ThisWorkbook module if you want it to respond to dashboard link clicks.
How do I hide the target worksheet again after jumping to it?
Add a Worksheet_Deactivate event on the target sheet or create a return macro that sets the sheet's Visible property to xlSheetHidden or xlSheetVeryHidden and then activates the dashboard worksheet.
