How to create a macro to unprotect multiple sheets using VBA?

To create a macro in VBA (Visual Basic for Applications) that unprotects multiple sheets in an Excel workbook, follow these steps:

Sub UnprotectAllSheets()
    Dim ws As Worksheet
    Dim pwd As String

    ' Assign your password to the variable. If no password, leave as empty string.
    pwd = "" ' If there's a password, put it between the quotes, e.g., pwd = "yourpassword"

    For Each ws In ThisWorkbook.Worksheets
        On Error Resume Next ' Continue if unprotecting fails (e.g., if sheet isn't protected)
        ws.Unprotect Password:=pwd
        If Err.Number <> 0 Then
            MsgBox "Error unprotecting " & ws.Name & ". Please check your password.", vbExclamation
            Err.Clear
        End If
        On Error GoTo 0 ' Reset error handling
    Next ws

    MsgBox "All sheets have been unprotected.", vbInformation
End Sub

Open Excel and Access the VBA Editor:

Open your Excel workbook.
Press `ALT + F11` to open the VBA editor.

Insert a New Module:

In the VBA editor, go to the menu and click `Insert` > `Module`. This creates a new module where you can write your macro.

Write the Macro:

Enter the following VBA code in the module window. This code will loop through each worksheet in the workbook and unprotect it:

Customize the Macro:

If your sheets are protected with a password, replace `pwd = “”` with your actual password, such as `pwd = “yourpassword”`. If there is no password, you can leave it as an empty string.

Run the Macro:

Close the VBA editor to return to Excel.
Go to the `Developer` tab on the Ribbon. If you don’t see it, you might need to enable it through Excel Options.
Click on `Macros`, select `UnprotectAllSheets`, and click `Run`.

Testing and Troubleshooting:

Ensure all sheets are unprotected. If there’s an error due to password issues, recheck the password string in the macro.
Consider using `On Error` approaches carefully; it’s used here to skip errors when sheets are not protected or if a wrong password is provided.

This macro should help you unprotect all worksheets in a workbook quickly. Ensure you save your workbook with macros enabled (as a `.xlsm` file) to retain the macro.

facebooklinkedin