Protect / Unprotect All Sheets BUT Exclude Pivot Table

  • Thread starter Thread starter Potsy
  • Start date Start date
P

Potsy

I have added a macro (shown below) to protect / unprotect all which
works well. Unfortunately, one of the sheets does now not update as it
is a pivot table graph and when I unlock all it works again. The
sheets are called "Report" and "Analysis", is there any way to add an
extra line to exclude on Protect. these sheets have macro buttons
contained within them which also need to be clickable.

Thanks in advance

-----

Sub ProtectAllSheets()
Application.ScreenUpdating = False
Dim N As Single
For N = 1 To Sheets.Count
Sheets(N).Protect Password:="admin"
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:="admin"
Next N
Application.ScreenUpdating = True
End Sub
 
Hi

Amend your code as follows

Sub ProtectAllSheets()
Application.ScreenUpdating = False
Dim N As Single
For N = 1 To Sheets.Count
If Sheets(N).Name <> "Report" And Sheets(N).Name <> "Analysis" Then
Sheets(N).Protect Password:="admin"
End If
Next N
Application.ScreenUpdating = True
End Sub
 
Hi

Amend your code as follows

Sub ProtectAllSheets()
Application.ScreenUpdating = False
Dim N As Single
For N = 1 To Sheets.Count
If Sheets(N).Name <> "Report" And Sheets(N).Name <> "Analysis" Then
Sheets(N).Protect Password:="admin"
End If
Next N
Application.ScreenUpdating = True
End Sub

--
Regards

Roger Govier








- Show quoted text -
Click to expand...

thanks for fast response - will give it a go!!
 
Hi

Amend your code as follows

Sub ProtectAllSheets()
Application.ScreenUpdating = False
Dim N As Single
For N = 1 To Sheets.Count
If Sheets(N).Name <> "Report" And Sheets(N).Name <> "Analysis" Then
Sheets(N).Protect Password:="admin"
End If
Next N
Application.ScreenUpdating = True
End Sub

--
Regards

Roger Govier








- Show quoted text -
Click to expand...

Thanks, however, now have "Run Time Error 1004 - RefreshTable Method
of PivotTable class failed" of in VBA when pivot table report
protected? When I unprotect all and click to update graph on pivot
table it is fine. Any ideas? cheers
 
Hi

Works fine for me.
When you check the 2 sheets after running the code, are they protected
or not?
What is the code that you are running, when you get the error you
posted?
 
Back
Top