| Home |
| Search |
| Today's Posts |
|
#5
|
|||
|
|||
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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Pivot Table Protection | Excel Discussion (Misc queries) | |||
| pivot table - keep text wrapped on refresh? | Excel Worksheet Functions | |||
| Pivot Table Refresh Problems | Excel Discussion (Misc queries) | |||
| Printing a Pivot Table from code - Excel 2003 | Excel Discussion (Misc queries) | |||
| ability to auto file column(s) on a pivot table | Excel Worksheet Functions | |||
Threaded Mode
