Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
KG
 
Posts: n/a
Default 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.

  #2   Report Post  
Debra Dalgleish
 
Posts: n/a
Default

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

  #3   Report Post  
KG
 
Posts: n/a
Default

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


  #4   Report Post  
Debra Dalgleish
 
Posts: n/a
Default

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

  #5   Report Post  
KG
 
Posts: n/a
Default

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




  #6   Report Post  
Posted to microsoft.public.excel.misc
Pastel Hughes
 
Posts: n/a
Default 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


  #7   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Pivot Table Protection mikeb Excel Discussion (Misc queries) 3 February 2nd 05 08:15 PM
pivot table - keep text wrapped on refresh? Cheryl Excel Worksheet Functions 1 February 2nd 05 07:19 PM
Pivot Table Refresh Problems PFL Excel Discussion (Misc queries) 2 January 13th 05 02:24 AM
Printing a Pivot Table from code - Excel 2003 Newbie Excel Discussion (Misc queries) 4 January 5th 05 05:10 PM
ability to auto file column(s) on a pivot table km Excel Worksheet Functions 1 December 20th 04 10:39 PM


All times are GMT +1. The time now is 12:44 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"