Protect & Unprotect Worksheets in Excel VBA

In this post, you’ll be learning how you can protect and unprotect worksheets in Excel VBA with some examples of code snippet.

Unprotect Excel Worksheet without Password

To unprotect a Excel Worksheet without Password

Code:

Worksheets("Sheet1").Unprotect

Unprotect Excel Worksheet with Password

To unprotect a Worksheet that is password-protected,

Code:

Worksheets("Sheet1").Unprotect "Password"

Unprotect Sheet – Lost Password

To unprotect a Worksheet if you forget the password, you must use a password recovery add-in.

Protect Worksheets

Worksheet Protection allows you to lock certain aspects of the sheet from editing. The protect feature is available in the format menu.

You can access the menu by,

• Under Home, click Format

• Click Protect sheet

Or, you can simply right-click on the Sheet tab name.

Protect Worksheet – Without Password

To protect a worksheet without password:

Code

Worksheets("Sheet1").Protect

Protect Worksheet – Password Protect

To protect a worksheet using password:

Code

Worksheets("Sheet1").Protect "Password"

Protect Worksheet Settings

If you want to custom protect your worksheet,

Code

Worksheets("Sheet1").Protect Password:=strPassword, DrawingObjects:=True, Contents:=True, Scenarios:=True, _
UserInterfaceOnly:=True, AllowFormattingCells:=False, AllowFormattingColumns:=False, _
AllowFormattingRows:=False, AllowInsertingColumns:=False, AllowInsertingRows:=False, _
AllowInsertingHyperlinks:=False, AllowDeletingColumns:=False, AllowDeletingRows:=False, _
AllowSorting:=False, AllowFiltering:=False, AllowUsingPivotTables:=False

OR

You can record a macro with the setting that you need and you can copy paste the code into the above syntax.

Unprotect All Sheets Using Macro

To unprotect all sheets using macro

Sub UnProtectUsingMacro()

    Dim ws As Worksheet

    For Each ws In Worksheets

        ws.Unprotect "password"

    Next ws

End Sub

Protect All Sheets Macro

To protect all sheets using macro

Sub ProtectUsingMacro ()

    Dim ws As Worksheet

    For Each ws In Worksheets

        ws.Protect "password"

    Next ws

End Sub

Using the above features, you can protect and unprotect the worksheets in Excel VBA.