VBA code to Protect and Unprotect Sheets

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.  It’s even worse when that person is us, all it takes is one accidental key press and suddenly the entire worksheet is filled with errors.

Worksheet protection is particularly frustrating because you have to apply it one sheet at a time.  If you only need to protect a single sheet, that’s fine.  But if you have 15 sheets to protect with a password, it is going to take a while.  This is why so many people turn to a VBA solutions.

The VBA Code Snippets below show how to do most activities related to protecting and unprotecting worksheets.

Adapting the code for your purposes

Every example below is based on one specific worksheet.  Each code starts with:

Sheets("Sheet1").

But there are lots of ways to reference sheets for protecting or unprotecting.

Using the active sheet

The active sheet is the whichever sheet is currently being used within the Excel window.

ActiveSheet.Protect

Looping through all worksheets

If we want to apply protection to every worksheet you could use the following code.

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

    ws.Protect

Next ws

Protect and unprotect: basic examples

The macros below show how to protect and unprotect a specific worksheet.

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

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

Checking if a worksheet is protected

To check if a worksheet is already protected, you can adapt the following code which is currently set to display a message box if the sheet 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

Adding a password to the protection is possible with VBA.

'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

If you forget a password, don’t worry, the protection is easy enough to break.

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

When a worksheet is protected only unlocked items can be edited.  The following codes demonstrate how to lock ranges, cells, charts, shapes 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

Even when protected, we can allow certain actions, such as inserting rows, formatting cells, sorting etc.

'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

There is one secret setting, which is only available through VBA.  This setting will allow VBA to make changes to the worksheet even when it is 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