VBA & Macro Problems

How to Protect All Excel Worksheets and Control Cell Selection with VBA

Ayan Masood Oct 22, 2026 868 views

Question details

The user wants to protect every worksheet in an Excel workbook at the same time and control the cell selection behavior for locked and unlocked cells using a VBA macro.

How to Protect All Excel Worksheets and Control Cell Selection with VBA

Product: Excel
Device & OS: not provided
Scenario: Securing an entire workbook by applying password protection to all sheets while managing which cells users are permitted to click or interact with.
Observed behavior: Currently, sheets have to be protected individually. The goal is to automate the bulk protection process and configure the EnableSelection property programmatically across all sheets.

Before you start

Before running the macro, note down the password you intend to use. Once the script protects all sheets, you will need this exact password to unprotect them later to make structural changes.

Solution 1Recommended

Use a VBA Macro to Protect All Sheets and Configure Selection

Execute a VBA loop to iterate through every worksheet in the workbook, set the selection restrictions, and apply password protection simultaneously.

Using a simple For Each loop in VBA allows you to apply the same protection settings across all sheets in seconds, bypassing the tedious process of protecting them one by one.

Open the VBA Editor

Press 'Alt + F11' in Excel to open the Visual Basic for Applications (VBA) editor.

Insert a New Module

Click 'Insert' from the top menu and select 'Module' to create a new blank script window.

Paste the Macro Code

Copy and paste the following code into the module window: Sub ProtectAllSheets() Dim sh As Worksheet For Each sh In Worksheets sh.EnableSelection = xlNoRestrictions sh.Protect Password:="password" Next sh End Sub Replace 'password' with your desired secure password.

Run the Macro

Press 'F5' or click the 'Run' icon in the toolbar to execute the script. All worksheets in the current workbook will now be protected.

Use a VBA Macro to Protect All Sheets and Configure Selection

Understanding EnableSelection Options: To prevent users from selecting locked cells, change 'xlNoRestrictions' to 'xlUnlockedCells' in the script. To block all cell selection entirely, use 'xlNoSelection'.

Automate and Protect with WPS Office

Protect Multiple Worksheets Effortlessly in WPS Spreadsheet

WPS Spreadsheet fully supports VBA macros, allowing you to seamlessly run your scripts to protect all worksheets at once. It also provides an intuitive user interface to manage sheet protection effortlessly.

1. Open your Workbook in WPS: Launch WPS Spreadsheet and open the file you wish to secure.
2. Access the VBA Editor: Navigate to the 'Tools' or 'Developer' tab and click on 'VBA Editor' to input your macro.
3. Run the Protection Script: Paste your worksheet protection script and press 'F5' to apply passwords to all sheets instantly.

Fully compatible with Microsoft Excel VBA macros (.xlsm formats).Intuitive UI to manually lock cells and protect sheets with a few clicks.Lightweight software that loads large workbooks and processes macros quickly.Free to use with comprehensive spreadsheet functionalities.

microsoft office alternative - wps office

Frequently Asked Questions

How do I unprotect all sheets using VBA?

You can use a similar loop in VBA. Write a macro that iterates through each worksheet and uses the unprotect command: `sh.Unprotect Password:="password"`.

Will this macro protect hidden worksheets as well?

Yes, the `For Each sh In Worksheets` loop applies to all worksheets in the workbook, regardless of whether their visibility is set to visible, hidden, or very hidden.

Why isn't my VBA macro saving with my file?

Standard Excel files (.xlsx) cannot store macros. You must 'Save As' and change the file format to an Excel Macro-Enabled Workbook (.xlsm) to retain the VBA code for future use.

Can I leave the password blank in the VBA script?

Yes, if you omit the `Password:="password"` part or leave the quotes empty `Password:=""`, the sheet will be protected without a password. Users will be able to click 'Unprotect Sheet' without being prompted for one.

Excel & Data Problems