I was recently answering a post on Ozgrid about filtering a list using a slicer. The post was very similar to a blog post of mine with a slight twist. The poster wanted the original list filtered based on the selection of the slicer and if no slicer item was selected then the filter was to be taken off the dataset. I found the problem interesting because in order to solve the problem you had to know how many slicer items were in the list in the first place.
How to solve a problem like this? Well firstly you need to generate code which detects a change in the slicer. The first thing to remember when dealing in slicers is you are not trapping a change in the slicer but a change in the source data, the pivot table. So by trapping a change in the pivot table in turn you trap a change in the slicer.
In short we need a worksheet change event. The following goes in the worksheet that the slicer is on.
Private Sub Worksheet_PivotTableChangeSync(ByVal Target As PivotTable)
Dim slItem As SlicerItem
Dim i As Integer
Sheet1.AutoFilterMode = False
With ActiveWorkbook.SlicerCaches("Slicer_Data")
For Each slItem In .VisibleSlicerItems
If slItem.Selected = True Then i = i + 1
Next slItem
If i <> .SlicerItems.Count Then Sheet1.[a1:A100].AutoFilter 1, Sheet3.[a4]
End With
End Sub
Now the thing to remember about the data above is that the name of the slicer is called Slicer_Data. You can obtain the name of your slicer by right clicking on the slicer and choosing:
Slicer Settings
From the dialog which is displayed you can see at the top of screen that the text has the name of the slicer. Tricky to see if you don't know what you are looking for.
The list above has East as the selected slicer item. The corresponding source data looks as follows when this happens.
In the slicer above if the red cross is pressed the filter is taken off and the full list is shown. This is a very neat way to manage a list of data with an Excel slicer.
The following is the link to the Excel file which shows the workings of this procedure. The file is the second article.
