Pivot Table Protection

[Guest]

Pivot Tables in my workbooks cannot be refreshed if the sheet is protected.

Here's what I've done:

Set up my worksheet.
Formatted cells that I wish to be locked.
Selected Tools>Protect Workbook
Selected Tools>Protect Worksheet
Clicked the following checkboxes: Select unlocked cells, Use Pivot Table
reports

The actual area of the Pivot Table in my worksheet is unlocked. Whe I select
the pivot table in the protected sheet I cannont refresh, but I can format
and create charts.

Any help would be appreciated.

Mike

 

[Debra Dalgleish]

You could record code as you unprotect the sheet, refresh the pivot
table, and reprotect the sheet.

Then, run that code as required, e.g. after you've updated the source
data, or when the pivot sheet is activated.

 

[Guest]

Code?

I am setting up an estimating spreadsheet for out salespersons. I have
several sheets that feed data to a "working" sheet. The salepersons fill out
the remaining and in one instance I have used a pivot table to consolidate
and sort the data.

Excel Help tells me I should be able to "protect" the worksheet and allow
the pivot table to be refreshed by another user. I do this by choosing "Use
Pivot Table report" when I protect the sheet. This does not work. I have
tried setting up a new pivot table in a blank workbook and get the same
result. The "refresh" icon is ghosted in the protected sheets.

Is this a glitch, or is it me?

I'd like to keep this as simple as possible, as I am not the end user of the
spreadsheet. Do you mean to record a macro that unprotects, refreshes, and
protects the sheet? Can I link that code to a cell in the sheet so that when
my salespersons get to the pivot table they can hit one button to do it all?

Mike

 

[Debra Dalgleish]

Do you remember where you found that information in Excel's Help?

Yes, you can record a macro as you unprotect the sheet, refresh, then
reprotect. In the recorded code, you can add a password, e.g.:

'==========================
Sub RefreshPivot()

ActiveSheet.Unprotect Password:="MyPwd"
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
ActiveSheet.Protect Password:="MyPwd", _
DrawingObjects:=True, Contents:=True, _
Scenarios:=True, AllowUsingPivotTables:=True
End Sub
'=======================

Then, add a "Refresh" button to the worksheet, and assign that macro to
the button.