Protect/Unprotect Multiple Sheets With Full Options
 

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

Protect/Unprotect Multiple Sheets With Full Options
 

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 wrote:

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

Protect/Unprotect Multiple Sheets With Full Options
 

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" wrote:

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 wrote:

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

Protect/Unprotect Multiple Sheets With Full Options
 

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 wrote:

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" wrote:

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 wrote:

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


--

Dave Peterson

Protect/Unprotect Multiple Sheets With Full Options
 

Dave,

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


"Dave Peterson" wrote:

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 wrote:

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" wrote:

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 wrote:

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


--

Dave Peterson