Slicer selection based on cell value

brt21

New Member
Joined
Oct 1, 2015
Messages
25
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 :confused:

Here are some codes i used

Code:
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

Code:
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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

mjc543

New Member
Joined
Oct 25, 2015
Messages
6
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.
 
Last edited:

mjc543

New Member
Joined
Oct 25, 2015
Messages
6
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.
 
Last edited:

brt21

New Member
Joined
Oct 1, 2015
Messages
25
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.
 

Forum statistics

Threads
1,141,311
Messages
5,705,680
Members
421,405
Latest member
buster015

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top