VBA & Macro Problems

Fix Excel VBA Macro Only Unhides Two Sheets When Assigned to a Button

Olivia Miller Oct 1, 2026 868 views

Question details

The user needs to fix a VBA macro designed to unhide multiple worksheets based on specific conditions. It works perfectly from the Developer tab but fails to complete when assigned to a button.

How to Fix Excel VBA Macro That Unhides Only Two Sheets via Button

Product: Excel
Device & OS: not provided
Scenario: Running an automated VBA macro to unhide specific hidden sheets using a Form Control button.
Observed behavior: The macro unhides all expected worksheets when executed directly from the VBA editor but unexpectedly stops after revealing only two sheets when triggered by a button. Adding a delay circumvents the issue, but a clean solution without timing delays is required.

Before you start

Verify that your workbook is saved as a Macro-Enabled Workbook (.xlsm) and ensure that the workbook structure is not password protected, as protection prevents macros from changing sheet visibility.

Solution 1Recommended

Simplify VBA Loop and Fully Qualify Range References

Optimize the macro by explicitly defining which sheets your ranges belong to, preventing the macro from losing its reference when the active sheet changes during button execution.

When a macro executes differently via a button compared to the VBA Editor, it is almost always caused by unqualified range references. When you click a Form Control button, Excel assumes any generic 'Range' refers strictly to the active sheet containing the button. As the code begins unhiding sheets, the focus shifts, causing the loop to break or evaluate incorrectly. By fully qualifying your ranges and streamlining the visibility logic, you eliminate the need for artificial delays.

Open the VBA Editor

Press Alt + F11 on your keyboard to launch the VBA Editor, and double-click the Module containing your unhide macro from the Project Explorer pane.

Explicitly Define Sheet References

Locate any instances of Range or Cells in your loop. Modify them to include their parent sheet object. For example, change 'Range("A1")' to 'Worksheets("Form Control").Range("A1")'.

Set Permanent Sheets to Visible First

Update your code structure to set the required, permanent sheets to visible outside of the loop to simplify processing.

Refactor the Yes/No Visibility Loop

Rewrite the loop to read the sheet names and Yes/No values directly from the fully qualified Form Control sheet. Set each target sheet to 'xlSheetVisible' or 'xlSheetHidden' based on this evaluation, and remove any 'Application.Wait' lines.

Simplify VBA Loop and Fully Qualify Range References

Best Practice: Always ensure the sheet names listed in your control cells perfectly match the actual worksheet names, preventing 'Subscript out of range' errors when the loop runs.

Solution 2

Use an ActiveX Command Button

Switch from a standard Form Control button to an ActiveX button, which often handles complex macro execution and focus shifts more reliably.

WPS Spreadsheet Solutions

Manage Complex Spreadsheets and Macros Natively in WPS Office

WPS Office provides comprehensive Developer tools with full VBA support, enabling you to automate workbook tasks seamlessly. It executes complex loops and conditional formatting rapidly, ensuring your interactive dashboards and macros function flawlessly.

1. Download and Install WPS Office: Visit the official WPS Office website to download the free suite and install it on your device.
2. Open Your Macro-Enabled Workbook: Launch WPS Spreadsheets and open your .xlsm file. Ensure macros are enabled when prompted by the security warning.
3. Access the VBA Editor: Navigate to the Developer tab in the ribbon and click 'Visual Basic' to view, edit, and optimize your unhide sheet macros.

Fully compatible with Microsoft Excel .xlsm, .xlsx, and .xls formatsBuilt-in VBA editor for seamless macro creation and troubleshootingLightweight architecture ensures fast execution without unnecessary delaysFamiliar spreadsheet interface that requires no learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why do macros act differently when run from a button vs. the Developer tab?

When you run a macro from the VBA editor, the code directly interacts with the workbook as written. When triggered by a Form Control button, the active focus shifts to the sheet containing the button. If your VBA code uses generic references like 'Range' without specifying the sheet, it looks at the button's sheet instead of the intended data sheet, causing loops to fail prematurely.

Is it safe to use Application.Wait to fix macro bugs?

While using Application.Wait can sometimes bypass timing issues, it is generally considered a poor practice. It forcefully halts processing, leading to unresponsive workbooks. The root cause usually lies in unqualified ranges or inefficient loops, which should be corrected directly in the code rather than relying on delays.

How do I unhide multiple sheets at once without using VBA?

In modern versions of Excel and WPS Spreadsheets, you can unhide multiple sheets manually by right-clicking any visible sheet tab, selecting 'Unhide', holding down the Ctrl key, selecting all the sheets you wish to reveal from the list, and clicking OK.

Excel & Data Problems