Clearing a specific slicer before referencing it

Question

I am looking for a way to clear a specific slicer before the code below runs without clearing all slicers in the workbook. The target.address D11, D12, etc are in a table of contents (ultimately there will be about 40 of these), but if the selected slicer isn't cleared and someone clicks on a different cell that uses the same slicer, then it returns a debug message. I am also open to other ways around this issue.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.EnableEvents = False

If Target.Address = "$D$11" Then
    Sheets("Trend In Meals").Select
    ActiveSheet.PivotTables("PivotTable3").PivotFields ("Meal Occasion")
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Meal Occasion")
        .PivotItems("All Occasions - Main Meals and Between Meals").Visible = True
        .PivotItems("Total Main Meals").Visible = False
        .PivotItems("Breakfast (Includes Brunch)").Visible = False
        .PivotItems("Lunch").Visible = False
        .PivotItems("Dinner").Visible = False
        .PivotItems("Between Meal Occasions").Visible = False
    End With
    End If
If Target.Address = "$D$12" Then
    Sheets("Trend In Meals").Select
    ActiveSheet.PivotTables("PivotTable3").PivotFields ("Meal Occasion")
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Meal Occasion")
        .PivotItems("All Occasions - Main Meals and Between Meals").Visible = False
        .PivotItems("Total Main Meals").Visible = True
        .PivotItems("Breakfast (Includes Brunch)").Visible = False
        .PivotItems("Lunch").Visible = False
        .PivotItems("Dinner").Visible = False
        .PivotItems("Between Meal Occasions").Visible = False
    End With
    End If
    
   Application.EnableEvents = True
    End Sub

Is "Trend In Meals" the same sheet which has this event handler? — Tim Williams
So the cells that are selected are in a table of contents tab, then "trend in meals" is another tab (which is where the slicers are located). To clarify a bit, I only show 2 cells to be selected, but there will be a total of 5 that will send you to the "trend in meals" tab — jammij83
There are 6 PivotItems - is one always False ? What is the error message you get? — Tim Williams
So each of them will have 1 true, and 5 false. There will never be any with multiple true selections. The error it returns "run time error 1004 - Unable to set the Visible property of the PivotItem class" — jammij83

Tim Williams Tim Williams · Accepted Answer · 2021-04-12T16:08:28

You always need one item visible, so set that before hiding the others (untested so may need tweaking)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim addr As String, arrItems, arrCells, m, lbl, pi As PivotItem
    
    arrCells = Array("D11", "D12", "D13", "D14", "D15", "D16") 'cells to be selected
    
    arrItems = Array("All Occasions - Main Meals and Between Meals", _
                     "Total Main Meals", "Breakfast (Includes Brunch)", _
                     "Lunch", "Dinner", "Between Meal Occasions")
    
    If Target.Cells.CountLarge > 1 Then Exit Sub
    
    If Not Application.Intersect(Target, Me.Range("D11:D15")) Is Nothing Then
        
        addr = Target.Address(False, False)      'cell address
        m = Application.Match(addr, arrCells, 0) 'position in arrCells (1-based)
        lbl = arrItems(m - 1)                    'correspondint label (zero-based so adjust by -1)
        
        With Sheets("Trend In Meals").PivotTables("PivotTable3").PivotFields("Meal Occasion")
            .PivotItems(lbl).Visible = True 'set the visible one first
            For Each e In arrItems
                If e <> lbl Then .PivotItems(e) = False 'then hide the others
            Next e
        End With
        Sheets("Trend In Meals").Select
    End If
    
End Sub

Clearing a specific slicer before referencing it

1 Answers