How to Protect or Unprotect an Excel Sheet with a Checkbox in WPS Spreadsheets Using VBA
Question details
The user wants to use a checkbox control and VBA code to automatically protect or unprotect a worksheet based on the checkbox state.
- Product
- WPS Spreadsheets (Excel for Mac macro-compatible)
- Device & OS
- macOS
- Scenario
- A user is working in a macro-enabled spreadsheet on macOS and wants to toggle sheet protection by clicking a checkbox instead of opening the Protect Sheet dialog each time.
- Observed behavior
- The worksheet should become protected when the checkbox is checked and unprotected when it is cleared.
Make sure the Developer tab is enabled in WPS Spreadsheets and that you save the workbook as a macro-enabled .xlsm file. Also confirm that your macro security settings allow you to run VBA code.
Use a Form Control Checkbox and VBA to Automatically Protect or Unprotect a Worksheet
Use this when you want a single on-sheet checkbox to toggle worksheet protection without manually opening the Protect Sheet dialog every time.
The macro checks the checkbox value each time the control is clicked. If the value is 1, it calls Sheet1.Protect; otherwise, it calls Sheet1.Unprotect.
This approach works in WPS Spreadsheets and in Excel for Mac when macros are enabled and the workbook is saved as a macro-enabled file.
In WPS Spreadsheets, click the Menu icon and go to Options > Customize Ribbon, then enable Developer. If the Developer tab is already visible, skip this step.
Go to Developer > Insert > Form Controls and choose Check Box. Draw the checkbox on the sheet, then right-click it to edit its text or open Format Control.
Press Alt+F11 or open Developer > Visual Basic to start the VBA editor. Insert a module and paste the following macro: Sub CheckBox1_Click() With Sheet1 If .Shapes("Check Box 1").ControlFormat.Value = 1 Then .Protect Else .Unprotect End If End With End Sub
Right-click the checkbox on the worksheet and choose Assign Macro. Select CheckBox1_Click so the protection state updates each time the checkbox is clicked.
Save the workbook in a macro-enabled format such as .xlsm. Close and reopen the file, then enable macros when prompted so the checkbox runs the macro.
Protect or Unprotect the Sheet Manually from the Review Tab
Use this method for occasional protection changes when you do not want to store or run a VBA macro.
Automate Worksheet Protection with a Checkbox in WPS Spreadsheets
Use the built-in Form Control checkbox and VBA editor in WPS Spreadsheets to run Sheet1.Protect and Sheet1.Unprotect automatically. The workbook remains compatible with Excel macro-enabled formats, so you can reuse the same checkbox logic across WPS Office and Excel for Mac.
- 1. Open your macro-enabled workbook: Launch WPS Spreadsheets and open the existing Excel workbook that contains the checkbox, or create a new workbook for the protection macro.
- 2. Insert a checkbox Form control: Go to Developer > Insert and choose the Check Box control under Form Controls. Draw the checkbox onto the worksheet.
- 3. Add and assign the VBA macro: Open the VBA editor with Alt+F11, paste the CheckBox1_Click macro, then right-click the checkbox and choose Assign Macro to link it.
- 4. Save and run: Save the file as a macro-enabled .xlsm workbook, reopen it, and enable macros so the checkbox can protect or unprotect the sheet.
Frequently Asked Questions
Why does nothing happen when I check the checkbox?
Usually macros are disabled. Reopen the workbook and choose Enable Macros when prompted, then verify the checkbox is still assigned to CheckBox1_Click.
Can I use this checkbox VBA macro in both WPS Spreadsheets and Excel for Mac?
Yes, the same Form Control checkbox and VBA code pattern works in both. Save the workbook in a macro-enabled format such as .xlsm and use the actual sheet code name in the macro.
Where is the Developer tab in WPS Spreadsheets?
Click the Menu icon, go to Options > Customize Ribbon, and enable Developer. You can also press Alt+F11 to open the VBA editor directly.
How do I add a password to the checkbox protection macro?
Add a password argument to the Protect and Unprotect calls, for example .Protect Password:="1234" and .Unprotect Password:="1234".
