VBA Protect / Unprotect Worksheets

This tutorial will teach you everything about Excel Worksheet protection in VBA – How to Protect or Unprotect Worksheets in Excel.

Unprotect Excel Worksheet Without Password

To unprotect a Worksheet that isn’t password-protected use this simple line of code:

Worksheets("Sheet1").Unprotect

Unprotect Excel Worksheet With Password

To unprotect a Worksheet that’s password-protected, you must also enter the password:

Worksheets("Sheet1").Unprotect "Password"

Unprotect Sheet – Lost Password

To unprotect a Worksheet without knowing 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.

This menu is found in Home > Format > Protect sheet or by right-clicking on the Sheet tab name:

Most commonly this is used to protect “Locked” cells from editing, only allowing the end-user to edit certain cells.

You can lock cells by selecting them, and opening the Protection tab of the Cell Formatting Menu (CTRL + 1).

You can also prevent the user from changing the worksheet structure (inserting, deleting, or resizing Rows & Columns), or from interacting with AutoFilters, and much more.

Protect Worksheet – Without Password

You might want to protect a Worksheet without entering a password. This will prevent accidental changes to the worksheet, while giving the user access to make changes if desired.

Worksheets("Sheet1").Protect

Protect Worksheet – Password Protect

Worksheets("Sheet1").Protect "Password"

Protect Worksheet Settings

The above examples will protect Worksheets with the standard protection settings. Instead you might want to customize what is protected:

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

Instead of using the syntax above, I recommend recording a Macro with your desired settings (chosen with the Worksheet Protection menu above) and copying + pasting the recorded code into your procedure.

Protect Sheet – Allow VBA to Make Changes

By default, when you protect a sheet, the protection applies to VBA operations in addition to user actions. If VBA attempts to modify a locked cell, you will see a runtime error 1004. To avoid this, you could unprotect and re-protect your worksheets whenever VBA needs to interact with them:

Sub Edit_Sheet1()
    'Unprotect Sheet1
    Worksheets("Sheet1").Unprotect

    'Do Something to Sheet1

    'Reprotect Sheet1
    Worksheets("Sheet1").Protect
End Sub

However, it’s easy to forget to unprotect and/or re-protect your worksheets. This can can increase the probability of a coding error.

Instead, you can use the UserInterFaceOnly setting. When TRUE, worksheets will ONLY be protected from users, NOT from VBA. Your VBA code will be free to edit the worksheet just like if it was unlocked.

Two important points about UserInterFaceOnly:

• This setting is not available from the Worksheet Protection menu (shown above). It’s a setting that must be defined in VBA.
• The setting is not saved when you close a workbook. It must be re-defined each time a workbook is opened.

So in order to set the UserInterFaceOnly property, you should place the following Workbook_Open event procedure in ThisWorkbook module:

Private Sub Workbook_Open()
    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        ws.Protect UserInterfaceOnly:=True
    Next ws
End Sub

Workbook_Open is a special event procedure that will run each time the workbook is open.  It must be placed in the ThisWorkbook module. Alternatively, you could use the Auto_Open event procedure (not covered here).

Unprotect All Sheets Macro

This Macro will unprotect all Sheets in a workbook:

' UnProtect All Worksheets
Sub UnProtectAllSheets()
    Dim ws As Worksheet

    For Each ws In Worksheets
        ws.Unprotect "password"
    Next ws

End Sub

Protect All Sheets Macro

This Macro will protect all Sheets in a Workbook:

' Protect All Worksheets
Sub ProtectAllSheets()
    Dim ws As Worksheet

    For Each ws In Worksheets
        ws.Protect "password"
    Next ws

End Sub