Slicer selection based on cell value

[brt21]

Hi,

i have been trying for quite some time to auto select the slicers based on data validation list.

for example, i have data validation list in the range I7 : I16 and I used INDEX to obtain the respective values based on selection in I7 : I16.

I want slicer to select the values automatically based on the values in cells J7 : J16. I used several codes, but none of them seems to work; not even gives an error message

Here are some codes i used

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range, cell As Range
Set rng = Range("J7:J16")
Dim slItem As SlicerItem

Application.ScreenUpdating = False

For Each cell In rng
For Each slItem In ThisWorkbook.SlicerCaches("Slicer_Material2").SlicerItems
     If slItem.Name = .SlicerItems(cell).Name Then slItem.Selected = True
     
     End If
Next slItem
Next cell

End Sub

Sub SingleSelection()
Dim si As SlicerItem, fi As SlicerItem


With ActiveWorkbook.SlicerCaches(1)
    Set fi = .SlicerItems(CStr(Sheets("SalesOrders").Range("J7:J16")))
    fi.Selected = True
    For Each si In .VisibleSlicerItems
        If si.Name <> fi.Name Then si.Selected = False
    Next
End With
End Sub

 

[mjc543]

I ran into similar trouble when I started creating interactive dashboards about a year ago. While I can't help with the appropriate VBA code in the context of slicers themselves, I can tell my solution was to manipulate the pivot filters directly (not the slicers connected to the pivots). If you think about it, using VBA to manipulate slicers is kind of like using a hammer to hit a mallet to drive a nail head... (why not just hit the nail directly?). Keep in mind also that if you filter a pivot table for a certain value, the slicer will refresh itself to show the filtered value automatically, assuming it's connected to that same pivot table.

 

[mjc543]

Ninja tip: for those of you who like slicers, you can capture a slicer selection for use in other formulas by creating a separate "mini" pivot table that has only the slicer values in the Rows area of the pivot table. Then connect your existing slicer to the new mini-pivot (do not create a new slicer) by right-clicking and selecting "Report Filters." Important - your "mini" pivot must share the same cache (i.e. data source) as the "big" pivot table that the slicer is being used to filter; otherwise it will not show up in the Report Filters list.

 

[brt21]

the slicer i wanted to control is linked to 2 other slicers/pivot table in different sheets. if i make a change to the slicer, it inflicts the others too.. so, i guess rather than linking the cell to the report filter, the one i suggested is better.