How to?: Excel protect pivot table, but still have expandable functionality

Question

I have an excel powerpivot table and I've protected the sheet and cells.

1) I'm trying to have the pivot table to be functional and multiple users to expand/collapse fields while the whole sheet is protected.

2) Important: Each user will have their own unique filtered views and I don't want User 1 to be able to unfilter the pivot table to see User 2-10 data.

Right now the protection looks like either have FULL Pivot table functionality or not. I hid the Pivot Table filter drop down and protected the sheet.

I found a loophole to this, "Analyze -> Clear Filter" this would allow User 2-10 to see User 1 data, which I do not want.

Does anyone have an idea how to LOCK the Analyze Function on a pivot table, BUT allow Pivot table Expand/Collapse Functionality?

EDIT: Source data is from Power Query -> Power Pivot, and then a pivotTable is generated from PowerPivot.

Thanks!

****EDIT 2:***** I found a way to protect certain aspects of the Pivot Tables (I have two on the same sheet: I'll need a way to disable "CLEAR ALL or CLEAR FILTERS" now.

Sub LockPivotTable()
'pivot table lock
Dim pt As PivotTable
Dim pf As PivotField

On Error Resume Next

For Each pt In ActiveSheet.PivotTables
With pt
  .EnableDrilldown = True
  .EnableFieldList = False
  .EnableFieldDialog = False

  .PivotCache.EnableRefresh = True
  For Each pf In pt.PivotFields
    With pf
      .DragToPage = True
      .DragToRow = True
      .DragToColumn = True
      .DragToData = True
      .DragToHide = True
    End With
  Next pf

End With
Next pt
End Sub

Interesting question. How are users accessing the file? Is it stored somewhere central where they all access it? Or do you email it to them individually? Any thoughts on how you might determining which user is currently viewing the report? — jeffreyweir
I would be emailing individual files to each user based on their individual filters. I'm currently looking into automating and exporting based on the filters. I can do it manually for a select few, but automation is my ultimate goal on a larger scale. — Dataletx
Are there multiple PivotTables in the file? Or just the one? Do you want them to only see the PivotTable, or should they get the entire file, with just that one PivotTable 'locked'? Do you plan to put together a list of users, email addresses, and filter settings that should apply? What else can you tell us? — jeffreyweir
There are two PivotTables in the file that are directly linked to the filters. 1 Pivot table is a 2 column short summary, the 2nd Pivot Table has all of the details (this requires expand/collapse/sort functionality in the pivot table). I have a running list of users to filter by. — Dataletx
Another question: Where is the source data for the PivotTable? i.e. are the PivotTables based on an Excel range, or the datamodel, or an external database? — jeffreyweir

EEM EEM · Accepted Answer · 2016-09-29T20:17:23

The method you are planning to use is not secure at all. Even if you manage to protect the worksheets and workbook, allowing users to only expand/collapse the fields. As long as the PivotCache contains the entire data, using VBA, a New PivotTable can be created to access that PivotCache. My suggestion would be to slice the Source Data using VBA and send workbooks to users containing only the data they need to see.

How to?: Excel protect pivot table, but still have expandable functionality

1 Answers