How to update slicer cache with VBA

Question

I'm using Excel VBA to hide/show elements on slicer depending upon user selection.

I have the following code :

 Private Sub removeFilterWithSlicer()

 Dim slicerCache As slicerCache

 Set slicerCache = ThisWorkbook.SlicerCaches("Slicer_Channel1")

 slicerCache.SlicerItems("A").Selected = False
 slicerCache.SlicerItems("B").Selected = False
 slicerCache.SlicerItems("C").Selected = False
 slicerCache.SlicerItems("D").Selected = False
 slicerCache.SlicerItems("E").Selected = False
 slicerCache.SlicerItems("F").Selected = False

End Sub

where A, B etc. are names of elements in slicer. I've cross checked the name of slicer cache ("Slicer_Channel1"). The issue is that the elements don't get deselected as they are supposed to. When I'm debugging the code, I found that each element gets deselected one by one but as soon as I reach the end of procedure i.e. End Sub, they all get back to being in selected state.

Any pointers ?

Can you elaborate on exactly what you are trying to do? Do you want the code to clear the slicer, or do you want it to filter the slicer on one particular SlicerItem, or something else? — jeffreyweir, Sep 04 '17 at 04:05
@jeffreyweir Essentially I want to clear the slicer using this code and make selections on user events but as suggested by jsotola I'm not able to deselect all filters even with a mouse. Still open for suggestions — hbabbar, Sep 04 '17 at 20:10
Cool. This can be done. But first, can you elaborate a little further about how the user is interacting with the spreadsheet, and what you want to happen when they do so? It would be handy to know why you don't simply want a user to use the Slicer itself, rather than programmatically clearing the slicer. — jeffreyweir, Sep 05 '17 at 02:04
@jeffreyweir So, I have a various channels governed by checkboxes. So, basically if I want to include element A in my analysis, i'll check element A, and a text box would be made enabled to enter some value and after processing I need to ensure that only the elements selected by the user are selected in filter which in turn would govern all my pivot charts. Right now I'm doing something like this slicerCache.SlicerItems("A").Selected = True when A is selected and so on — hbabbar, Sep 05 '17 at 19:29
Okay. So you're effectively using CheckBoxes as a Slicer? Before we proceed, is there any specific design reason why you don't just use a Slicer, and do away with the need for checkboxes (and associated code) completely? You can select multiple items in a Slicer by using the Ctrl key as you click. (Apologies for asking, but my design philosophy is to always first consider what can be done natively in Excel before considering a code-based approach.) — jeffreyweir, Sep 05 '17 at 23:31
@jeffreyweir The main reason was to make only selected elements are shown in legend. There is a lot of preprocessing (basically if the user selects one element, he has the option of further selecting multiple time elements as well) so the code is necessary. — hbabbar, Sep 07 '17 at 09:59
Can you explain what you mean by "If the user selects one element, he has the option of selecting multiple time elements as well"? I don't understand. Slicers will let you select multiple items. If you need to select multilple items from multiple PIvotFields, just add a slicer for each PivotField you need the user to select something from, and tell them to hold down the Ctrl key when selecting multiple items. — jeffreyweir, Sep 08 '17 at 05:30
If you found my answer useful, can you mark it as such, or otherwise comment. — jeffreyweir, Sep 21 '17 at 22:37

jeffreyweir · Accepted Answer · 2017-09-21T23:05:34.643

This code shows how to filter a Slicer on an array called vSelection.

Option Explicit

Sub FilterSlicer()
Dim slr As Slicer
Dim sc As SlicerCache
Dim si As SlicerItem
Dim i As Long
Dim vItem As Variant
Dim vSelection As Variant

Set sc = ActiveWorkbook.SlicerCaches("Slicer_ID")
'Set sc = slr.SlicerCache

vSelection = Array("B", "C", "E")

For Each pt In sc.PivotTables
    pt.ManualUpdate = True 'Stops PivotTable from refreshing after each PivotItem is changed
Next pt

With sc

    'At least one item must remain visible in the Slicer at all times, so make the first
    'item visible, and at the end of the routine, check if it actually  *should* be visible
    .SlicerItems(1).Selected = True

    'Hide any other items that aren't already hidden.
    'Note that it is far quicker to check the status than to change it.
    ' So only hide each item if it isn't already hidden
    For i = 2 To .SlicerItems.Count
        If .SlicerItems(i).Selected Then .SlicerItems(i).Selected = False
    Next i

    'Make the PivotItems of interest visible
    On Error Resume Next 'In case one of the items isn't found
    For Each vItem In vSelection
        .SlicerItems(vItem).Selected = True
    Next vItem
    On Error GoTo 0

    'Hide the first PivotItem, unless it is one of the countries of interest
    On Error Resume Next
    If InStr(UCase(Join(vSelection, "|")), UCase(.SlicerItems(1).Name)) = 0 Then .SlicerItems(1).Selected = False
    If Err.Number <> 0 Then
        .ClearAllFilters
        MsgBox Title:="No Items Found", Prompt:="None of the desired items was found in the Slicer, so I have cleared the filter"
    End If
    On Error GoTo 0
End With


For Each pt In sc.PivotTables
    pt.ManualUpdate = False
Next pt

End Sub

score 0 · Answer 2 · answered Sep 04 '17 at 02:24

0

at least one has to be selected. works the same way when selecting with a mouse. you cannot un-select all

answered Sep 04 '17 at 02:24

jsotola

2,238
1
10
22

How to update slicer cache with VBA

2 Answers2

Linked