VBA code to Protect and Unprotect Worksheets

Protecting and unprotecting worksheets is a common action. There is nothing worse than somebody who doesn’t know what they’re doing over typing important formulas and cell values. Many advancing VBA programmers will start to consider how to apply worksheet protection automatically with Macros. The VBA Code Snippets below show how to do most activities related to protecting and unprotecting worksheets.

Basic protecting and unprotecting

'Protect a worksheet
Sheets("Sheet1").Protect

'Unprotect a worksheet
Sheets("Sheet1").Unprotect

Checking if a worksheet is protected

'Check if worksheets contents is protected
If Sheets("Sheet1").ProtectContents Then MsgBox "Protected Contents"

'Check if worksheet objects are protected
If Sheets("Sheet1").ProtectDrawingObjects Then MsgBox "Protected Objects"

'Check if worksheet scenarios are protected
If Sheets("Sheet1").ProtectScenarios Then MsgBox "Protected Scenarios"

Protecting and unprotecting with a password

'Protect worksheet with a password
Sheets("Sheet1").Protect Password:="myPassword"

'Unprotect a worksheet with a password
Sheets("Sheet1").Unprotect Password:="myPassword"

NOTE: It is not necessary to unprotect a worksheet to change the protection settings.

If an incorrect password is provided the following error message will show.

The code below will catch the error and provide a custom message.

'Catch an incorrect password
On Error Resume Next
Sheets("Sheet1").Unprotect Password:="incorrectPassword"

If Err.Number <> 0 Then
    MsgBox "The Password Provided is incorrect"
    Exit Sub
End If

On Error GoTo 0

Applying protection to different parts of the worksheet

VBA provides the ability to protect 3 aspects of the worksheet:

Contents – what you see on the grid
Objects – the shapes and charts which are on top of the grid
Scenarios – the scenarios contained in the What If Analysis section of the Ribbon

'Apply worksheet contents protection only
Sheets("Sheet1").Protect Password:="myPassword", _
    DrawingObjects:=False, _
    Contents:=True, _
    Scenarios:=False

'Apply worksheet objects protection only
Sheets("Sheet1").Protect Password:="myPassword", _
    DrawingObjects:=True, _
    Contents:=False, _
    Scenarios:=False

'Apply worksheet scenario protection only
Sheets("Sheet1").Protect Password:="myPassword", _
    DrawingObjects:=False, _
    Contents:=False, _
    Scenarios:=True

'Apply worksheet protection to contents, objects and scenarios
Sheets("Sheet1").Protect Password:="myPassword", _
    DrawingObjects:=True, _
    Contents:=True, _
    Scenarios:=True

Changing the locked or unlocked status of cells, objects and scenarios

'Changing the options to lock or unlock cells
Sheets("Sheet1").Range("A1").Locked = True

'Changing the options to lock or unlock cells all cells
Sheets("Sheet1").Cells.Locked = True

'Changing the options to lock or unlock charts
Sheets("Sheet1").ChartObjects("Chart 1").Locked = True

'Changing the option to lock or unlock shapes
Sheets("Sheet1").Shapes("Rectangle 1").Locked = True

'Changing the option to lock or unlock a scenario
Sheets("Sheet1").Scenarios("scenarioName").Locked = True

Allowing certain actions to be performed even when protected

'Allowing certain actions even if the worksheet is protected
Sheets("Sheet1").Protect Password:="myPassword", _
    DrawingObjects:=False, _
    Contents:=True, _
    Scenarios:=False, _
    AllowFormattingCells:=True, _
    AllowFormattingColumns:=True, _
    AllowFormattingRows:=True, _
    AllowInsertingColumns:=False, _
    AllowInsertingRows:=False, _
    AllowInsertingHyperlinks:=False, _
    AllowDeletingColumns:=True, _
    AllowDeletingRows:=True, _
    AllowSorting:=False, _
    AllowFiltering:=False, _
    AllowUsingPivotTables:=False

'Allowing selection of locked or unlocked cells
Sheets("Sheet1").EnableSelection = xlNoRestrictions

'Allowing selection of unlocked cells only
Sheets("Sheet1").EnableSelection = xlUnlockedCells

'Do not allow selection of any cells
Sheets("Sheet1").EnableSelection = xlNoSelection

Allowing VBA code to make changes, even when protected

'Enable changes to worksheet by VBA code, even if protected
Sheets("Sheet1").Protect Password:="myPassword", _
 UserInterfaceOnly:=True

Allowing the use of the Group and Ungroup feature

To allow the use of the Group and Ungroup feature of Excel, we need to allow changes to the user interface and enable outlining.

'Allow user to group and ungroup whilst protected
Sheets("Sheet1").Protect Password:="myPassword", _
 UserInterfaceOnly:=True

Sheets("Sheets1").EnableOutlining = True

Other related VBA code snippets

The following VBA Code Snippets maybe useful for applying this post in a wider context.