How to Show or Hide Protected Excel Worksheets with a Checkbox VBA Macro
Question details
The user needs a VBA macro to toggle the visibility of specific worksheets in a protected Excel workbook by clicking a checkbox linked to a cell.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Creating an interactive dashboard where users can check a box to reveal hidden supplementary sheets, requiring the macro to automatically unprotect, toggle visibility, and reprotect the sheets.
- Observed behavior
- A programmatic solution is required to detect the checkbox state, manage worksheet or workbook protection passwords, and adjust the Visible property of the target worksheets accordingly.
Ensure you have the Developer tab enabled to insert Form Controls and access the VBA editor. Note down your exact worksheet protection password, as you will need to embed it in the macro code.
Use a Linked Checkbox and Worksheet Change Event
Link a Form Control checkbox to a specific cell and use a Worksheet_Change macro to automatically unprotect, toggle visibility, and reprotect sheets.
This method uses a checkbox linked to cell C1. When the checkbox is clicked, the cell value changes, triggering the macro to unprotect sheets 2 through 6, update their visibility, and lock them again.
Go to the Developer tab, click Insert, and select the Checkbox (Form Control). Draw it on your first worksheet. Right-click the checkbox, select Format Control, and set the Cell link to C1.
Press Alt + F11 to open the Visual Basic for Applications editor. In the Project Explorer on the left, double-click the module for your first worksheet (e.g., Sheet1).
Paste the following code into the worksheet module. Replace 'your_password' with your actual password: Private Sub ShowSheets(ByVal sheetsVisible As Boolean) Dim i As Long, password As String password = "your_password" For i = 2 To 6 ThisWorkbook.Worksheets(i).Unprotect Password:=password ThisWorkbook.Worksheets(i).Visible = sheetsVisible ThisWorkbook.Worksheets(i).Protect Password:=password Next i End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$C$1" Then Application.ScreenUpdating = False ShowSheets (Target.Value = "") Application.ScreenUpdating = True End If End Sub
Close the VBA editor. Check and uncheck the box on your worksheet to verify that sheets 2 through 6 appear and disappear as expected.
Assign a Macro Directly to a Form Control Checkbox
If you do not need to unprotect sheets via code, you can assign a simpler macro directly to the checkbox to toggle visibility.
Simulate a Checkbox using the Double-Click Event
If you want to avoid using Form Controls entirely, you can use a double-click event on a specific cell to act as a simulated checkbox.
Create Powerful VBA Macros with WPS Spreadsheet
WPS Office fully supports VBA (Visual Basic for Applications) and Excel macros, allowing you to easily build interactive dashboards, insert checkboxes, and automate sheet protection tasks with code.
- 1. Enable Developer Tools: Open WPS Spreadsheet, go to the Developer tab, and click 'Visual Basic' to access the VBA programming environment.
- 2. Insert Form Controls: Under the Developer tab, select 'Insert' to easily add Checkboxes, Option Buttons, and Combo Boxes to your dashboard.
- 3. Write and Execute Code: Use the VBA editor to paste or write your automation scripts and safely save your file in macro-enabled (.xlsm) format.
Frequently Asked Questions
Why do I get a "Run-time error '1004'" when trying to hide a sheet via VBA?
This usually happens if the workbook's overall structure is protected. You must unprotect the workbook using `ThisWorkbook.Unprotect "password"` before changing a worksheet's `Visible` property, even if the individual sheets themselves are unprotected.
Can I hide the TRUE/FALSE text in the cell linked to the checkbox?
Yes, you can easily hide the value by changing the font color of the linked cell (e.g., C1) to match the cell's background color (usually white). Alternatively, you can place the linked cell in a hidden column or row.
Does changing the `Visible` property always require unprotecting the worksheet?
Not necessarily. Protecting a worksheet's contents usually restricts editing cells and objects, but it doesn't always lock its visibility. However, if the workbook structure is protected, or if your macro modifies locked cells as part of its process, unprotecting via VBA is required.
