Sheet protection code conflicts with Pivot Table "auto refresh"
 

I received the following code from Debra Dalgleish, to password protect all
worksheets, setting certain exceptions for objects, etc.


Public Sub ProtectAll()
Const PWORD As String = "mysecretword"
Dim wsSheet As Worksheet
For Each wsSheet In Worksheets
wsSheet.Protect Password:=PWORD, _
DrawingObjects:=False, _
AllowFiltering:=True, _
AllowUsingPivotTables:=True
Next wsSheet
End Sub

It works well, except for one thing: when I open the file, I get an error
message which emanates from the fact that I have selected "refresh
automatically" in the Pivot Table Options (the problem disappears when I
deselect the auto refresh box). I really don't want to disable that feature,
therefore, for the time being I go to the worksheet containing the Pivot
Table and manually remove all protection.

I presume that there is a way to resolve this conflict with VBA.

Thanks for your help.

Instead of setting the pivot tables to 'refresh on open', you could
refresh the pivot tables programmatically. Create a macro to unprotect
the sheets, refresh the pivot tables, and reprotect the sheets.

KG wrote:
I received the following code from Debra Dalgleish, to password protect all
worksheets, setting certain exceptions for objects, etc.


Public Sub ProtectAll()

Const PWORD As String = "mysecretword"
Dim wsSheet As Worksheet
For Each wsSheet In Worksheets
wsSheet.Protect Password:=PWORD, _
DrawingObjects:=False, _
AllowFiltering:=True, _
AllowUsingPivotTables:=True
Next wsSheet
End Sub


It works well, except for one thing: when I open the file, I get an error
message which emanates from the fact that I have selected "refresh
automatically" in the Pivot Table Options (the problem disappears when I
deselect the auto refresh box). I really don't want to disable that feature,
therefore, for the time being I go to the worksheet containing the Pivot
Table and manually remove all protection.

I presume that there is a way to resolve this conflict with VBA.

Thanks for your help.



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Can the following code (which I believe you also provided in recent months)
be used in tandem with the sheet protection code:

Private Sub Workbook_Open()
Dim ws As Worksheet
Dim pt As PivotTable

On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect Password:="SecretWord"
For Each pt In ws.PivotTables
pt.RefreshTable
Next
ws.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True, _
Password:="SecretWord"
Next

End Sub

If so, I presume the above code would go into "This Workbook." However I
already have the following short code in "This Workbook" and I am confused
whether or not the two pieces will need to be a single, continuous
"Workbook_open" code:

Private Sub Workbook_Open()
UserForm1.Show
Sheets("Instructions").Select
Range("A1").Select
End Sub

Thank you for your help.

"Debra Dalgleish" wrote:

Instead of setting the pivot tables to 'refresh on open', you could
refresh the pivot tables programmatically. Create a macro to unprotect
the sheets, refresh the pivot tables, and reprotect the sheets.

KG wrote:
I received the following code from Debra Dalgleish, to password protect all
worksheets, setting certain exceptions for objects, etc.


Public Sub ProtectAll()

Const PWORD As String = "mysecretword"
Dim wsSheet As Worksheet
For Each wsSheet In Worksheets
wsSheet.Protect Password:=PWORD, _
DrawingObjects:=False, _
AllowFiltering:=True, _
AllowUsingPivotTables:=True
Next wsSheet
End Sub


It works well, except for one thing: when I open the file, I get an error
message which emanates from the fact that I have selected "refresh
automatically" in the Pivot Table Options (the problem disappears when I
deselect the auto refresh box). I really don't want to disable that feature,
therefore, for the time being I go to the worksheet containing the Pivot
Table and manually remove all protection.

I presume that there is a way to resolve this conflict with VBA.

Thanks for your help.



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Yes, you can only have one Workbook_Open procedure in the workbook, so
you should combine them.

Add your three existing lines to the end of the longer procedure, and
store the new procedure in the ThisWorkbook module.

KG wrote:
Can the following code (which I believe you also provided in recent months)
be used in tandem with the sheet protection code:

Private Sub Workbook_Open()
Dim ws As Worksheet
Dim pt As PivotTable

On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect Password:="SecretWord"
For Each pt In ws.PivotTables
pt.RefreshTable
Next
ws.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True, _
Password:="SecretWord"
Next

End Sub

If so, I presume the above code would go into "This Workbook." However I
already have the following short code in "This Workbook" and I am confused
whether or not the two pieces will need to be a single, continuous
"Workbook_open" code:

Private Sub Workbook_Open()
UserForm1.Show
Sheets("Instructions").Select
Range("A1").Select
End Sub

Thank you for your help.

"Debra Dalgleish" wrote:


Instead of setting the pivot tables to 'refresh on open', you could
refresh the pivot tables programmatically. Create a macro to unprotect
the sheets, refresh the pivot tables, and reprotect the sheets.

KG wrote:

I received the following code from Debra Dalgleish, to password protect all
worksheets, setting certain exceptions for objects, etc.


Public Sub ProtectAll()


Const PWORD As String = "mysecretword"
Dim wsSheet As Worksheet
For Each wsSheet In Worksheets
wsSheet.Protect Password:=PWORD, _
DrawingObjects:=False, _
AllowFiltering:=True, _
AllowUsingPivotTables:=True
Next wsSheet
End Sub


It works well, except for one thing: when I open the file, I get an error
message which emanates from the fact that I have selected "refresh
automatically" in the Pivot Table Options (the problem disappears when I
deselect the auto refresh box). I really don't want to disable that feature,
therefore, for the time being I go to the worksheet containing the Pivot
Table and manually remove all protection.

I presume that there is a way to resolve this conflict with VBA.

Thanks for your help.



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

thank you. Problem solved

"Debra Dalgleish" wrote:

Yes, you can only have one Workbook_Open procedure in the workbook, so
you should combine them.

Add your three existing lines to the end of the longer procedure, and
store the new procedure in the ThisWorkbook module.

KG wrote:
Can the following code (which I believe you also provided in recent months)
be used in tandem with the sheet protection code:

Private Sub Workbook_Open()
Dim ws As Worksheet
Dim pt As PivotTable

On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect Password:="SecretWord"
For Each pt In ws.PivotTables
pt.RefreshTable
Next
ws.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True, _
Password:="SecretWord"
Next

End Sub

If so, I presume the above code would go into "This Workbook." However I
already have the following short code in "This Workbook" and I am confused
whether or not the two pieces will need to be a single, continuous
"Workbook_open" code:

Private Sub Workbook_Open()
UserForm1.Show
Sheets("Instructions").Select
Range("A1").Select
End Sub

Thank you for your help.

"Debra Dalgleish" wrote:


Instead of setting the pivot tables to 'refresh on open', you could
refresh the pivot tables programmatically. Create a macro to unprotect
the sheets, refresh the pivot tables, and reprotect the sheets.

KG wrote:

I received the following code from Debra Dalgleish, to password protect all
worksheets, setting certain exceptions for objects, etc.


Public Sub ProtectAll()


Const PWORD As String = "mysecretword"
Dim wsSheet As Worksheet
For Each wsSheet In Worksheets
wsSheet.Protect Password:=PWORD, _
DrawingObjects:=False, _
AllowFiltering:=True, _
AllowUsingPivotTables:=True
Next wsSheet
End Sub


It works well, except for one thing: when I open the file, I get an error
message which emanates from the fact that I have selected "refresh
automatically" in the Pivot Table Options (the problem disappears when I
deselect the auto refresh box). I really don't want to disable that feature,
therefore, for the time being I go to the worksheet containing the Pivot
Table and manually remove all protection.

I presume that there is a way to resolve this conflict with VBA.

Thanks for your help.



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Sheet protection code conflicts with Pivot Table "auto refresh
 

This code worked perfectly for me, however anyone opening the file has only
to look at the code to find out what the password is. Is there any way to
avoid this?

"KG" wrote:

Can the following code (which I believe you also provided in recent months)
be used in tandem with the sheet protection code:

Private Sub Workbook_Open()
Dim ws As Worksheet
Dim pt As PivotTable

On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect Password:="SecretWord"
For Each pt In ws.PivotTables
pt.RefreshTable
Next
ws.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True, _
Password:="SecretWord"
Next

End Sub

If so, I presume the above code would go into "This Workbook." However I
already have the following short code in "This Workbook" and I am confused
whether or not the two pieces will need to be a single, continuous
"Workbook_open" code:

Private Sub Workbook_Open()
UserForm1.Show
Sheets("Instructions").Select
Range("A1").Select
End Sub

Thank you for your help.

"Debra Dalgleish" wrote:

Instead of setting the pivot tables to 'refresh on open', you could
refresh the pivot tables programmatically. Create a macro to unprotect
the sheets, refresh the pivot tables, and reprotect the sheets.

KG wrote:
I received the following code from Debra Dalgleish, to password protect all
worksheets, setting certain exceptions for objects, etc.


Public Sub ProtectAll()

Const PWORD As String = "mysecretword"
Dim wsSheet As Worksheet
For Each wsSheet In Worksheets
wsSheet.Protect Password:=PWORD, _
DrawingObjects:=False, _
AllowFiltering:=True, _
AllowUsingPivotTables:=True
Next wsSheet
End Sub


It works well, except for one thing: when I open the file, I get an error
message which emanates from the fact that I have selected "refresh
automatically" in the Pivot Table Options (the problem disappears when I
deselect the auto refresh box). I really don't want to disable that feature,
therefore, for the time being I go to the worksheet containing the Pivot
Table and manually remove all protection.

I presume that there is a way to resolve this conflict with VBA.

Thanks for your help.



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Sheet protection code conflicts with Pivot Table "auto refresh
 

You can protect your project.

Next time you're in the VBE, select your project.
tools|vbaproject properties|protection tab
Lock project for viewing and give it a memorable password.

(Be aware that the password won't stop the really curious.)

Pastel Hughes wrote:

This code worked perfectly for me, however anyone opening the file has only
to look at the code to find out what the password is. Is there any way to
avoid this?

"KG" wrote:

Can the following code (which I believe you also provided in recent months)
be used in tandem with the sheet protection code:

Private Sub Workbook_Open()
Dim ws As Worksheet
Dim pt As PivotTable

On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect Password:="SecretWord"
For Each pt In ws.PivotTables
pt.RefreshTable
Next
ws.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True, _
Password:="SecretWord"
Next

End Sub

If so, I presume the above code would go into "This Workbook." However I
already have the following short code in "This Workbook" and I am confused
whether or not the two pieces will need to be a single, continuous
"Workbook_open" code:

Private Sub Workbook_Open()
UserForm1.Show
Sheets("Instructions").Select
Range("A1").Select
End Sub

Thank you for your help.

"Debra Dalgleish" wrote:

Instead of setting the pivot tables to 'refresh on open', you could
refresh the pivot tables programmatically. Create a macro to unprotect
the sheets, refresh the pivot tables, and reprotect the sheets.

KG wrote:
I received the following code from Debra Dalgleish, to password protect all
worksheets, setting certain exceptions for objects, etc.


Public Sub ProtectAll()

Const PWORD As String = "mysecretword"
Dim wsSheet As Worksheet
For Each wsSheet In Worksheets
wsSheet.Protect Password:=PWORD, _
DrawingObjects:=False, _
AllowFiltering:=True, _
AllowUsingPivotTables:=True
Next wsSheet
End Sub


It works well, except for one thing: when I open the file, I get an error
message which emanates from the fact that I have selected "refresh
automatically" in the Pivot Table Options (the problem disappears when I
deselect the auto refresh box). I really don't want to disable that feature,
therefore, for the time being I go to the worksheet containing the Pivot
Table and manually remove all protection.

I presume that there is a way to resolve this conflict with VBA.

Thanks for your help.



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html



--

Dave Peterson