Using VBA to restrict pivot table changes - reordering all columns and rows

[raaaaaaah]

Hi,

I've got a workbook with multiple pivot tables on each sheet that I would like users to have limited use to.

I found the following code online which is successful except that I want to limit the ability to reorder all columns (including values columns). Is there a way to limit that?

>>> use code - tags <<<

Sub RestrictPivotTable()
'pivot table tutorial by contextures.com
Dim pf As PivotField
On Error Resume Next

With ActiveSheet.PivotTables(1)
  .EnableDrilldown = True
  .EnableFieldList = False  
  .EnableFieldDialog = False
  .PivotCache.EnableRefresh = True
  For Each pf In .PivotFields
    With pf
      .DragToPage = False
      .DragToRow = False
      .DragToColumn = False
      .DragToData = False
      .DragToHide = False
    End With
  Next pf
End With

End Sub

 

[vletm]

raaaaaaah​

Have You tested sheets protection option?

 

[raaaaaaah]

Yes but I there are functions that I would still like to allow such as drilldown, sort and filter. I couldn't find a way to get drilldown to work on a protected sheet

 

[vletm]

So ... protection works 'too well' with Your case.
Could You create a sample Excel-file,
which shows
# what do You really would like to allow?
# what do You really would like to restrict?
Or
Could You add eg to the 1st row of that sheet values from 1 to 9999 ( = number of columns )
... and take care that those values keep in same order?
... if someone makes 'mistake' and has changed order of those value ... then do Undo.

 

[Monty]

This should help

Sub RestrictPivotTable()
    Dim pf As PivotField
    On Error Resume Next
    With ActiveSheet.PivotTables(1)
        .EnableDrilldown = True
        .EnableFieldList = False
        .EnableFieldDialog = False
        .PivotCache.EnableRefresh = True
        For Each pf In .PivotFields
            With pf
                .DragToPage = False
                .DragToRow = False
                .DragToColumn = False
                .DragToData = False
                .DragToHide = False
                .Orientation = xlHidden ' Hide the field from the pivot table layout
            End With
        Next pf
    End With
End Sub

 

[Monty]

In the updated code, I added `.Orientation = xlHidden` inside the loop that iterates through each pivot field.

This line hides each pivot field from the pivot table layout, effectively preventing users from dragging and reordering them.