Hello,
I have a pivot table, which I restricted from editing with a macro. Associated with this pivot, there is a slicer, which I need to be restricted much the same way. I have tried changing its settings to no avail (it is currently set to ‘unlocked’, ‘disable resizing and moving’, and ‘don’t move with size or cells’). I have a feeling there must be a method I could use to prevent it from being selected and edited while maintaining its functionality.
The code I use to restrict the pivot is provided below. I’ve tried to disable the slicer from being selected and edited as a Range but unsuccessfully. Is there something I’m not seeing? Any ideas would be über appreciated.
Thank you in advance.
I also attach here an example of the dilemma in question.
Code:
Private Sub DisableSelection()[/COLOR][/FONT]
[FONT=Tahoma][COLOR=#000000]Set pt = ActiveSheet.PivotTables(1)[/COLOR][/FONT]
[FONT=Tahoma][COLOR=#000000] For Each pf In pt.RowFields[/COLOR][/FONT]
[FONT=Tahoma][COLOR=#000000] pf.EnableItemSelection = False[/COLOR][/FONT]
[FONT=Tahoma][COLOR=#000000] Next pf[/COLOR][/FONT]
[FONT=Tahoma][COLOR=#000000]End Sub[/COLOR][/FONT]
[FONT=Tahoma][COLOR=#000000]Private Sub RestrictPivotTable()[/COLOR][/FONT]
[FONT=Tahoma][COLOR=#000000]With ActiveSheet.PivotTables(1)[/COLOR][/FONT]
[FONT=Tahoma][COLOR=#000000] .EnableWizard = False[/COLOR][/FONT]
[FONT=Tahoma][COLOR=#000000] .EnableDrilldown = False[/COLOR][/FONT]
[FONT=Tahoma][COLOR=#000000] .EnableFieldList = False 'Excel 2002+[/COLOR][/FONT]
[FONT=Tahoma][COLOR=#000000] .EnableFieldDialog = False[/COLOR][/FONT]
[FONT=Tahoma][COLOR=#000000] .PivotCache.EnableRefresh = False[/COLOR][/FONT]
[FONT=Tahoma][COLOR=#000000] For Each pf In .PivotFields[/COLOR][/FONT]
[FONT=Tahoma][COLOR=#000000] With pf[/COLOR][/FONT]
[FONT=Tahoma][COLOR=#000000] .DragToPage = False[/COLOR][/FONT]
[FONT=Tahoma][COLOR=#000000] .DragToRow = False[/COLOR][/FONT]
[COLOR=#000000][FONT=Tahoma].DragToColumn = False[/FONT][/COLOR]
[FONT=Tahoma][COLOR=#000000] .DragToData = False[/COLOR][/FONT]
[FONT=Tahoma][COLOR=#000000] .DragToHide = False[/COLOR][/FONT]
[COLOR=#000000][FONT=Tahoma]End With[/FONT][/COLOR]
[FONT=Tahoma][COLOR=#000000] Next pf[/COLOR][/FONT]
[FONT=Tahoma][COLOR=#000000]End With[/COLOR][/FONT]
[FONT=Tahoma][COLOR=#000000]End Sub[/COLOR][/FONT]
Attachments
Last edited by a moderator: