Results 1 to 2 of 2

Thread: Restricting a slicer from being edited

  1. #1

    Restricting a slicer from being edited

    Register for a FREE account, and/
    or Log in to avoid these ads!


    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.

    Private Sub DisableSelection()
    Set pt = ActiveSheet.PivotTables(1)
      For Each pf In pt.RowFields
          pf.EnableItemSelection = False
      Next pf
    End Sub
    Private Sub RestrictPivotTable()
    With ActiveSheet.PivotTables(1)
      .EnableWizard = False
      .EnableDrilldown = False
      .EnableFieldList = False   'Excel 2002+
      .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
    Attached Files Attached Files
    Last edited by Bob Phillips; 2014-03-19 at 04:26 PM. Reason: Added VBA tags

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Excel Version
    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
        Application.EnableEvents = True
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts