How to Unhide Excel Worksheets from Shape Buttons Using VBA
Question details
The user needs to click a shape button to execute a VBA macro that unhides and activates a specific hidden worksheet.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Creating an interactive navigation menu or dashboard where clicking shape buttons directs users to hidden sheets.
- Observed behavior
- Hyperlinks attached to shapes fail to trigger the worksheet hyperlink event because Excel lacks a dedicated shape hyperlink event, preventing the hidden sheet from opening automatically.
Ensure your workbook is saved as an Excel Macro-Enabled Workbook (.xlsm) and that you have enabled macros in the Developer tab settings.
Remove Hyperlink and Assign a Custom VBA Macro
The most reliable method is to remove the native hyperlink from the shape and assign a standard VBA macro that explicitly unhides and activates the target sheet.
Excel's Worksheet_FollowHyperlink event only triggers for hyperlinks embedded within worksheet cells. Since shape objects do not possess a Shape_FollowHyperlink event, standard hyperlinks on shapes will not trigger VBA event code.
To achieve the desired behavior, you must create a standard module macro and assign it directly to the shape.
Right-click the shape you are using as a button and select 'Remove Hyperlink' from the context menu. If the native hyperlink remains, the shape will attempt to follow it instead of running the macro.
Press 'Alt + F11' on your keyboard to open the Visual Basic for Applications (VBA) editor. Click 'Insert' in the top menu bar, then select 'Module' to create a new standard module.
In the new module window, paste the following code: Sub UnhideSheet() Sheets("TargetSheetName").Visible = True Sheets("TargetSheetName").Activate End Sub Ensure you replace 'TargetSheetName' with the actual name of your hidden worksheet.
Close the VBA editor and return to your Excel worksheet. Right-click the shape button, select 'Assign Macro...', choose your 'UnhideSheet' macro from the list, and click 'OK'.
Use Macros to Unhide Worksheets in WPS Office
WPS Spreadsheet fully supports VBA and macros, allowing you to easily assign scripts to shapes to unhide sheets and build fully interactive workbooks with an intuitive interface.
- 1. Open your workbook: Launch WPS Spreadsheet and open your macro-enabled workbook. Navigate to the Developer tab on the top ribbon.
- 2. Launch the VBA Editor: Click on the 'Visual Basic' icon (or press Alt + F11) to open the VBA Editor environment.
- 3. Insert the macro code: Go to Insert > Module, paste your standard worksheet unhide code into the module, and then close the editor.
- 4. Assign the macro to a shape: Right-click your desired shape on the spreadsheet, select 'Assign Macro' from the dropdown, choose your script, and click 'OK' to finish linking.
Frequently Asked Questions
Why doesn't the Worksheet_FollowHyperlink event work for shape buttons?
The Worksheet_FollowHyperlink event is exclusively designed to be triggered by hyperlinks placed within worksheet cells. Excel does not have an equivalent Shape_FollowHyperlink event, so clicking a shape with a hyperlink bypasses worksheet event triggers.
Do I absolutely need to remove the existing hyperlink from the shape?
Yes. If a native hyperlink remains attached to the shape, clicking the button will execute the hyperlink action rather than the assigned VBA macro, causing your unhide script to fail.
Will this macro work if the shape's text is different from the worksheet name?
Absolutely. Because the macro explicitly references the target worksheet's actual name in the VBA code (e.g., Sheets("Sheet2").Visible = True), the display text on the shape button is completely independent and does not need to match.
How do I hide the worksheet again after viewing it?
You can create a secondary macro (e.g., Sheets("TargetSheetName").Visible = False) and assign it to a 'Back' or 'Close' shape button located on the target sheet itself. This allows users to easily return to the main menu and re-hide the worksheet in one click.
