How to protect all sheets in a workbook at once using VBA?

To protect all sheets in a workbook at once using VBA, you can create a macro that loops through each worksheet and applies protection to it. Here’s a simple example of how you can do this:

Sub ProtectAllSheets()
    Dim ws As Worksheet
    Dim password As String
    
    ' Set your desired password here
    password = "YourPassword"
    
    ' Loop through each worksheet in the workbook
    For Each ws In ThisWorkbook.Worksheets
        ws.Protect Password:=password
    Next ws
    
    ' Notify the user
    MsgBox "All sheets have been protected."
End Sub

How to Use the Macro:

Open the workbook where you want to protect all the sheets.
Press `ALT` + `F11` to open the Visual Basic for Applications (VBA) editor.
Insert a new module:

In the VBA editor, click on `Insert` from the top menu.
Click on `Module` to insert a new module.

Copy and paste the code into the module window.
You can customize the `password` variable at the beginning of the code with a password of your choice.
Close the VBA editor and go back to Excel.
Run the Macro:

Press `ALT` + `F8` to open the “Macro” dialog box.
Select `ProtectAllSheets` from the list and click `Run`.

Important Considerations:

Password Security: Make sure you remember the password you set; otherwise, you will not be able to unprotect the sheets without it.
Macro Security Settings: Ensure that your Excel settings allow macros to run. You may need to adjust your security settings to enable macros if they are disabled by default.
Testing: It might be helpful to test this macro on a copy of your workbook first to ensure that it performs as expected and that you have access to the password.

This script will lock all cells in each worksheet by default. If there are specific cells that need to remain editable, these should be formatted as unlocked before running the macro.

facebooklinkedin