Protect/Unprotect Multiple Sheets With Full Options

[TEK]

I currently have a macro which allows me to protect/unprotect multiple
wortksheets. However, how do I expand the code to apply all the protection
options when protecting? I keep receiving errors when manipulating the code
after referencing it from the macro recorder. My current code is as follows:


Sub ProtectAllSheets()
Application.ScreenUpdating = False
Dim N As Single
For N = 1 To Sheets.Count
Sheets(N).Protect Password:="password"
Next N
Application.ScreenUpdating = True
End Sub

Sub UnprotectAllSheets()
Application.ScreenUpdating = False
Dim N As Single
For N = 1 To Sheets.Count
Sheets(N).Unprotect Password:="password"
Next N
Application.ScreenUpdating = True
End Sub

 

[Dave Peterson]

Record a macro when you manually protect a worksheet with the options you want.

Then try to incorporate that into your code.

If you have trouble, post that code and indicate the line that's causing the
trouble.

 

[TEK]

Hi Dave,

When I add the below, which is all the protection options checked, I receive
a "object required" error. I changed "Active" sheet to "Any".

AnySheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingColumns:=True,
AllowInsertingRows _
:=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True

 

[Dave Peterson]

You'd want to use Sheets(N):

Option Explicit
Sub ProtectAllSheets()
Application.ScreenUpdating = False
Dim N As Long
For N = 1 To Sheets.Count
Sheets(N).Protect _
Password:="password", _
DrawingObjects:=False, _
Contents:=True, _
Scenarios:=False, _
AllowFormattingCells:=True, _
AllowFormattingColumns:=True, _
AllowFormattingRows:=True, _
AllowInsertingColumns:=True, _
AllowInsertingRows:=True, _
AllowInsertingHyperlinks:=True, _
AllowDeletingColumns:=True, _
AllowDeletingRows:=True, _
AllowSorting:=True, _
AllowFiltering:=True, _
AllowUsingPivotTables:=True
Next N
Application.ScreenUpdating = True
End Sub

 

[TEK]

Dave,

This worked perfectly! Thanks for your help, it's much appreciated.