VBA & Macro Problems

How to Use VBA to Lock and Protect All Excel Worksheets

Amos Gikunda Oct 10, 2026 868 views

Question details

The user needs a VBA script to automatically lock all cells and protect all worksheets whenever an Excel workbook is opened.

Product: Excel
Device & OS: not provided
Scenario: Opening an Excel workbook and ensuring all worksheets revert to a locked and protected default state for end users.
Observed behavior: A Workbook_Open event is required to sequentially unprotect, lock all cells, and re-protect every worksheet using a designated password.

Before you start

Ensure your workbook is saved as an Excel Macro-Enabled Workbook (.xlsm) format, and verify that you know the current password for any already-protected sheets before running the macro.

Solution 1Recommended

Use Workbook_Open VBA to Lock and Protect Sheets

Apply a macro in the ThisWorkbook module to loop through all worksheets, locking cells and applying password protection automatically upon opening.

This method utilizes the Workbook_Open event to reset the protection state of all sheets every time the file is opened. It is highly useful for shared workbooks where users might forget to re-protect sheets after editing data.

Open the VBA Editor

Press Alt + F11 on your keyboard while in Excel to open the Visual Basic for Applications (VBA) editor.

Access the ThisWorkbook Module

In the Project Explorer pane on the left side of the window, find your workbook name and double-click on 'ThisWorkbook' to open its code window.

Insert the VBA Code

Paste the following code into the window: Private Sub Workbook_Open() Dim wsh As Worksheet For Each wsh In Me.Worksheets wsh.Unprotect Password:="secret" wsh.Cells.Locked = True wsh.Protect Password:="secret" Next wsh End Sub Be sure to replace "secret" with your actual preferred password.

Save as Macro-Enabled Workbook

Click File > Save As, and choose 'Excel Macro-Enabled Workbook (*.xlsm)' from the file format dropdown menu to ensure your macro is preserved.

Use Workbook_Open VBA to Lock and Protect Sheets

Security Limitation: Worksheet protection is not a strict security measure. A knowledgeable user can bypass automatic macros by holding the Shift key while opening the workbook.

Seamless VBA Support in WPS Office

Run VBA Macros Easily in WPS Spreadsheet

WPS Office provides excellent built-in support for VBA macros, allowing you to automate tasks, lock cells, and protect worksheets effortlessly, just as you would in Microsoft Excel.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your .xlsm workbook.
2. Enable Macros: Navigate to the 'Developer' tab in the top ribbon and select 'Enable Macros' if prompted by the security warning.
3. Access the VBA Editor: Click the 'Visual Basic' button under the Developer tab or press Alt + F11 to view and edit your Workbook_Open macro.
4. Save your progress: Use standard save options to keep your macro changes intact within the highly compatible WPS environment.

Fully compatible with Microsoft Excel Macro-Enabled (.xlsm) formats.Supports standard VBA syntax and Workbook_Open macro execution.Free, lightweight, and features an intuitive tabbed interface.Seamless migration of complex spreadsheet tools without losing functionality.

Frequently Asked Questions

Why doesn't my Workbook_Open macro run when I open the file?

Macros might be disabled in your Excel security settings, or the user may have held the Shift key while opening the file, which natively overrides automatic startup macros. Ensure macros are enabled in your Trust Center settings.

Can I apply this VBA script to lock specific sheets instead of all of them?

Yes. Instead of using a 'For Each' loop across 'Me.Worksheets', you can specify individual sheets by replacing the loop with direct references, such as Sheets("Sheet1").Protect Password:="secret".

Is worksheet password protection secure against hackers?

No. Worksheet protection is designed to prevent accidental edits by standard users and organize workflow, not to securely encrypt sensitive data. Password-protected worksheets can be bypassed by experienced users.

Excel & Data Problems