Protect or Unprotect Worksheets

Protect or Unprotect Worksheets

The following code snippets either protect or unprotect all worksheets in a workbook. Change the value within the quotes to set the password.

[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
Option Explicit

Public Sub ProtectAllSheets()
Dim objSheet As Worksheet
For Each objSheet In Worksheets
If objSheet.ProtectContents = False Then objSheet.Protect "P@ssw0rd"
Next objSheet
End Sub

Public Sub UnProtectAllSheets()
Dim objSheet As Worksheet
For Each objSheet In Worksheets
If objSheet.ProtectContents = True Then objSheet.Unprotect "P@ssw0rd"
Next objSheet
End Sub
[/sourcecode]

Here’s how it works
If you are having trouble running code on protected worksheets you may have to unprotect the sheet before the code runs. To run code on a protected sheet you can set it up as follows:

[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
Sub WorkOnProtectedSheet()
ActiveSheet.Unprotect password:="addPasswordHere"

‘Add code here

ActiveSheet.Protect password:="addPasswordHere", _
AllowFormattingCells:=True, AllowFormattingColumns:=True, _
Contents:=True
End Sub
[/sourcecode]

Here is a list of options when protecting a worksheet (use them as  item:=value)

  • Password
  • DrawingObjects
  • Contents
  • Scenarios
  • UserInterfaceOnly
  • AllowFormattingCells
  • AllowFormattingColumns
  • AllowFormattingRows
  • AllowInsertingColumns
  • AllowInsertingRows
  • AllowInsertingHyperlinks
  • AllowDeletingColumns
  • AllowDeletingRows
  • AllowSorting
  • AllowFiltering
  • AllowUsingPivotTables