Refreshing Pivot Table on a protected spreadsheet tab

[EarlyBirdie]

Cell A1 on tab AAA is unprotected and can be updated with a users pass code
which, in turn, automatically updates formulas (that use data from hidden
tabs) in protected-cells B1 through K1000. These cells can be viewed but not
selcted by users.

Cells A1002 through K1100 contain a pivot table that uses the contents of
cells B1 through K1000 as its source to summarize the contents of that range.


After users, enter their respective pass codes in cell A1, I want them to be
able to refresh the pivot table but Excel does not provide that as an option
as long as the spreadsheet is protected, even though I have the ticked the
"Use Pivot Table Reports" in the protection parameters. The cells in the
pivot table range are unprotected.

How can I keep cells A1002 through K1100 protected and still allow users to
refresh the pivot table? I know I can reposition the pivot table on an
unprotected tab and have formulas on tab AAA pointing to the pivot table but
I'm looking for a simpler solution.

 

[Shane Devenshire]

Hi,

Here is one way, by adding this code the the sheet object for the sheet with
the pivot table:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("A1"))
If Not isect Is Nothing Then
With ActiveSheet
.Unprotect Password:="x"
.PivotTables("PivotTable1").PivotCache.Refresh
.Protect Password:="x"
End With
End If
End Sub

Of course you will need to change the password.