Lock changes to Pivot Table Rules

[amsaini15]

Hi All
I have tried goggling this for several hours but cannot find a VBA code/method to lock down any changes on Pivot Table.

I still would like to use Pivot Table and would want it to refresh when Data changes but dont want anybody to make any changes to the Pivot Table structure.

Please help . Thanks

[mancubus]

welcome to vbax

this can help:

http://www.contextures.com/xlPivot12.html

[amsaini15]

Thanks mancubus.

I am actually after way to stop changes to Row Labels, Column Labels, Values, etc or in short how to stop user to change the Pivot Table desizn by adding deleting more rows/columns. I dont want 'Pivot Table Field List' to popping out each time I click on Pivot Table cell.

Above link provided good information to remove dropdown from report filter but I am afraid I was still able to add another report filter and remove row label.

Please let me know if I need to make it more clearer . Thanks

[Aflatoon]

Which code were you using - the RestrictPivotTable routine?

[amsaini15]

Thanks for your reply. Didn't know about RestrictPivotTable until you mentioned.
Is this the code I need to use? - www . mrexcel.com/forum/excel-questions/440695-better-solution-current-visual-basic-applications-code.html

[Aflatoon]

It's in the link that mancubus posted earlier.

[amsaini15]

Thanks Aflatoon. So it seems we cannot protect Pivot Table rows/column structure to be changed without disabling Refresh? One way or the other? Ideally, would not prefer running protect/un-protect macro for refreshing only.

"However, once the sheet is protected, you can’t create a new pivot table. Also, you won't be able to refresh a pivot table on the worksheet, because that feature is disabled on a protected sheet. If you right-click in a pivot table cell, the Refresh command is disabled."

[Aflatoon]

This is the code from the link.


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

With ActiveSheet.PivotTables(1)
.EnableDrilldown = False
.EnableFieldList = False
.EnableFieldDialog = False
.PivotCache.EnableRefresh = False
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

If you want to be able to refresh the pivot table, remove this line:

.PivotCache.EnableRefresh = False