VBA & Macro Problems

How to Set Excel Worksheet Protection Options in VBA

Khadija Khan Oct 21, 2026 868 views

Question details

The user needs to maintain specific worksheet protection settings, such as allowing comments and editing objects, when unprotecting and reprotecting a sheet using VBA.

How to Set Excel Worksheet Protection Options in VBA

Product: Microsoft Excel
Device & OS: not provided
Scenario: Running a VBA macro that unprotects and reprotects worksheets, which unintentionally resets custom protection permissions.
Observed behavior: Worksheet protection settings default back to strict restrictions after the macro runs, preventing users from adding comments or editing objects.

Before you start

Before modifying your VBA code, ensure you have enabled the Developer tab in your ribbon to access the Visual Basic Editor, and save a backup copy of your workbook.

Solution 1Recommended

Specify Protection Arguments in the VBA Protect Method

Modify your VBA macro to explicitly state which actions are allowed when the script reprotects the worksheet.

When a macro runs the standard Protect command without arguments, Excel applies default strict protection. To preserve user permissions, you must define them within the code.

Open the Visual Basic Editor

Press ALT + F11 on your keyboard to launch the VBA Editor.

Locate the relevant macro

In the Project Explorer, double-click the Module or Worksheet containing the macro that handles the unprotect and protect actions.

Update the Protect statement

Modify the reprotect line to include your desired arguments. For example, use: `ws.Protect AllowAddingComments:=True, UserInterfaceOnly:=True`.

Enable drawing object editing

To allow users to add or edit comments (which Excel classifies as drawing objects), add `DrawingObjects:=False` to your Protect statement.

Specify Protection Arguments in the VBA Protect Method

Understanding Drawing Objects in VBA: In Excel VBA, cell comments are treated as drawing objects. Setting DrawingObjects to False is necessary if you want users to be able to insert and modify comments on a protected sheet.

Manage VBA Macros Seamlessly with WPS Spreadsheet

WPS Spreadsheet offers robust support for VBA macros, allowing you to easily run, edit, and troubleshoot your Excel macro scripts, including worksheet protection configurations.

1. Open your macro workbook: Launch WPS Spreadsheet and open your existing macro-enabled workbook.
2. Access the Developer Tab: Navigate to the Developer tab on the ribbon and click on 'Visual Basic' to open the editor.
3. Edit the protection code: Update your VBA script to include explicit arguments like `DrawingObjects:=False` in the Protect method.
4. Save and execute: Save your changes and run the macro directly within WPS Spreadsheet to apply the customized protection settings.

Fully compatible with Microsoft Excel VBA macros (.xlsm formats)Lightweight architecture for fast execution of complex scriptsIntuitive Developer tab for streamlined macro managementFree to use with a familiar, easy-to-navigate interface

microsoft office alternative - wps office

Frequently Asked Questions

Why do my worksheet protection settings reset after running a macro?

When a macro executes the `Protect` method without specifying any arguments, the application applies the default, most restrictive protection settings, overwriting any custom permissions you previously set manually.

What does the UserInterfaceOnly argument do in VBA?

Setting `UserInterfaceOnly:=True` allows your VBA macros to modify the protected worksheet without needing to unprotect it first, while still restricting manual edits by the user.

How do I allow users to format cells in a VBA-protected sheet?

You can allow cell formatting by adding the `AllowFormattingCells:=True` argument to your `Protect` method statement within the VBA macro.

Are VBA macros written in Excel compatible with WPS Office?

Yes, WPS Office Spreadsheet provides comprehensive support for Microsoft Excel VBA macros, allowing you to run, edit, and save your existing .xlsm files without compatibility issues.

Excel & Data Problems