title: How to?: Excel protect pivot table, but still have expandable functionality
body: <p>I have an excel powerpivot table and I've protected the sheet and cells.</p>
  
  <p>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.</p>
  
  <p>2) <strong>Important:</strong> 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.</p>
  
  <p>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.</p>
  
  <p><strong>I found a loophole to this, "Analyze -> Clear Filter"</strong> this would allow User 2-10 to see User 1 data, which I do not want.</p>
  
  <p><strong>Does anyone have an idea how to LOCK the Analyze Function on a pivot table, BUT allow Pivot table Expand/Collapse Functionality?</strong></p>
  
  <p><strong>EDIT:</strong> Source data is from Power Query -> Power Pivot, and then a pivotTable is generated from PowerPivot.</p>
  
  <p>Thanks!</p>
  
  <p>****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.</p>
  
  <pre><code>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
  </code></pre>