Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table in Protected Worksheet
I have a pivot table that is refreshed automatically through a macro. Works
great. The line of code in the macro that runs the refresh is ActiveSheet.PivotTables("PivotTable2").PivotCache. Refresh Now I want to protect the worksheet that the pivot table is on. When I protect the worksheet and perform the action that launches the macro, I keep getting an error that indicates "Run-Time error '1004': That command cannot be performed while a protected sheet contains another pivot table report based on the same source data" The pivot table does not update. I do not have another pivot table on the worksheet. I don't even have another pivot table in the same file. The pivot table is based on data on a different worksheet (same workbook file though) and this worksheet is also protected. (If this matters at all) I am operating Excel 2003 Any takers? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table in Protected Worksheet
With activesheet
.unprotect password:="topSecret" .pivottables("pivottable2").pivotcache.refresh .protect password:="topSecret" end with imapilot_152 wrote: I have a pivot table that is refreshed automatically through a macro. Works great. The line of code in the macro that runs the refresh is ActiveSheet.PivotTables("PivotTable2").PivotCache. Refresh Now I want to protect the worksheet that the pivot table is on. When I protect the worksheet and perform the action that launches the macro, I keep getting an error that indicates "Run-Time error '1004': That command cannot be performed while a protected sheet contains another pivot table report based on the same source data" The pivot table does not update. I do not have another pivot table on the worksheet. I don't even have another pivot table in the same file. The pivot table is based on data on a different worksheet (same workbook file though) and this worksheet is also protected. (If this matters at all) I am operating Excel 2003 Any takers? -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table in Protected Worksheet
I am embarrassed it is so easy. I bow to your problem-solving skills.
Thank you very much for your rapid (and working) solution! "Dave Peterson" wrote: With activesheet .unprotect password:="topSecret" .pivottables("pivottable2").pivotcache.refresh .protect password:="topSecret" end with imapilot_152 wrote: I have a pivot table that is refreshed automatically through a macro. Works great. The line of code in the macro that runs the refresh is ActiveSheet.PivotTables("PivotTable2").PivotCache. Refresh Now I want to protect the worksheet that the pivot table is on. When I protect the worksheet and perform the action that launches the macro, I keep getting an error that indicates "Run-Time error '1004': That command cannot be performed while a protected sheet contains another pivot table report based on the same source data" The pivot table does not update. I do not have another pivot table on the worksheet. I don't even have another pivot table in the same file. The pivot table is based on data on a different worksheet (same workbook file though) and this worksheet is also protected. (If this matters at all) I am operating Excel 2003 Any takers? -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table in Protected Worksheet
Hello:
I tried to use the following code but get the error stating: Code used: Sub Test() With ActiveSheet .Unprotect Password:="test1" .PivotTables("PivotTable1").PivotCache.Refresh .Protect Password:="test1" End With End Sub Error recvd.: Run-time error '1004'. Cannot run MSQUERY.exe. Can you please advice? I am able to refresh the Pivot Table manually without any problem - I am also able to refresh it when the spreadsheet is not password protected. The security for macros is set to low. And I do not have the "Refresh on Open" option marked. Thanks. Forge "imapilot_152" wrote: I am embarrassed it is so easy. I bow to your problem-solving skills. Thank you very much for your rapid (and working) solution! "Dave Peterson" wrote: With activesheet .unprotect password:="topSecret" .pivottables("pivottable2").pivotcache.refresh .protect password:="topSecret" end with imapilot_152 wrote: I have a pivot table that is refreshed automatically through a macro. Works great. The line of code in the macro that runs the refresh is ActiveSheet.PivotTables("PivotTable2").PivotCache. Refresh Now I want to protect the worksheet that the pivot table is on. When I protect the worksheet and perform the action that launches the macro, I keep getting an error that indicates "Run-Time error '1004': That command cannot be performed while a protected sheet contains another pivot table report based on the same source data" The pivot table does not update. I do not have another pivot table on the worksheet. I don't even have another pivot table in the same file. The pivot table is based on data on a different worksheet (same workbook file though) and this worksheet is also protected. (If this matters at all) I am operating Excel 2003 Any takers? -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table in Protected Worksheet
Hello:
I found my answer - used the following code to get what I needed: Private Sub Workbook_Open() With ActiveSheet .Unprotect Password:="test2" ActiveWorkbook.RefreshAll End With End Sub Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable) ActiveSheet.Protect Password:="test2", _ 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 End Sub "Forge" wrote: Hello: I tried to use the following code but get the error stating: Code used: Sub Test() With ActiveSheet .Unprotect Password:="test1" .PivotTables("PivotTable1").PivotCache.Refresh .Protect Password:="test1" End With End Sub Error recvd.: Run-time error '1004'. Cannot run MSQUERY.exe. Can you please advice? I am able to refresh the Pivot Table manually without any problem - I am also able to refresh it when the spreadsheet is not password protected. The security for macros is set to low. And I do not have the "Refresh on Open" option marked. Thanks. Forge "imapilot_152" wrote: I am embarrassed it is so easy. I bow to your problem-solving skills. Thank you very much for your rapid (and working) solution! "Dave Peterson" wrote: With activesheet .unprotect password:="topSecret" .pivottables("pivottable2").pivotcache.refresh .protect password:="topSecret" end with imapilot_152 wrote: I have a pivot table that is refreshed automatically through a macro. Works great. The line of code in the macro that runs the refresh is ActiveSheet.PivotTables("PivotTable2").PivotCache. Refresh Now I want to protect the worksheet that the pivot table is on. When I protect the worksheet and perform the action that launches the macro, I keep getting an error that indicates "Run-Time error '1004': That command cannot be performed while a protected sheet contains another pivot table report based on the same source data" The pivot table does not update. I do not have another pivot table on the worksheet. I don't even have another pivot table in the same file. The pivot table is based on data on a different worksheet (same workbook file though) and this worksheet is also protected. (If this matters at all) I am operating Excel 2003 Any takers? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Refreshing Pivot Table on a protected spreadsheet tab | Excel Discussion (Misc queries) | |||
Enable the page area only of a pivot table for protected worksheet | Excel Discussion (Misc queries) | |||
Pivot Table Use When Sheet is Protected | Excel Discussion (Misc queries) | |||
Enable OLAP Pivot Table in Protected Excel 2003 Worksheet | Excel Programming | |||
Enable OLAP Pivot Table in Protected Excel 2003 Worksheet | Excel Programming |