Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Refreshing Pivot Table on a protected spreadsheet tab
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. -- EarlyBirdie |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Refreshing Pivot Table on a protected spreadsheet tab
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. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "EarlyBirdie" wrote: 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. -- EarlyBirdie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Refreshing a pivot table | Excel Discussion (Misc queries) | |||
Pivot Table Refreshing | Excel Discussion (Misc queries) | |||
Pivot table refreshing | Excel Discussion (Misc queries) | |||
When refreshing pivot tables my pivot table chart type changes | Excel Discussion (Misc queries) | |||
Refreshing a pivot table | Excel Discussion (Misc queries) |