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?

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

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

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

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