Restricting a slicer from being edited

nea

New member
Joined
Mar 19, 2014
Messages
1
Reaction score
0
Points
0

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

  • Ex.xlsm
    22.2 KB · Views: 6
Last edited by a moderator:

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Code:
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim pt As PivotTable
Dim pf As PivotField


    On Error GoTo wp_exit
    
    Application.EnableEvents = False
    
    Set pt = ActiveSheet.PivotTables(1)
    
    For Each pf In pt.PivotFields
      
        For Each Pi In pf.PivotItems
        
            Pi.Visible = True
        Next Pi
    Next pf


wp_exit:
    Application.EnableEvents = True
End Sub
 
Top