Excel VBA Slicer Selection

Slicers are one of the most attractive ways to show summary data.  Introduced in Excel 2010 slicers are a way to show a list of data as buttons on a page.

Swinging Muramasa Sword from Vector.me (by qubodup)

Clicking on the buttons enables you to isolate one item in a list of items.

The slicer above the summary table shows a summary (All).  Next to this table I want to show a chart displaying data stacked if All is chosen or a sigle region if one of those is displayed in the slicer box.

However, I want to do this so it is seemless. So the user does not know there is going to be some VBA unleashed on the file.  I first create two charts, 1 Stacked Bar chart and the other a 2D Column chart.  Make these charts identical and overlay the charts. Take note of the chart names when these are created as this will become important in the coding process.

Create a slicer and take note of the name of the slicer.  This can be done by right clicking on the slicer and in the dialog which is shown;

The default name for a slicer is Slicer_TheNameofYourPivotTableColumn

The Excel VBA to achieve this is as follows;

Option Explicit

Private Sub Worksheet_PivotTableChangeSync(ByVal Target As PivotTable)

Dim slItem As SlicerItem
With ActiveWorkbook.SlicerCaches("Slicer_Data")

For Each slItem In .VisibleSlicerItems

If slItem.Name = "All" Then

ActiveSheet.Shapes("Chart 1").ZOrder msoSendToBack

Else

ActiveSheet.Shapes("Chart 2").ZOrder msoSendToBack

End If

Next slItem

End With

End Sub

Lessons from the code to ensure it works with your example.

The code needs to go in the worksheet code module that your pivot table is contained on.

The variable needs to match your example;

If slItem.Name = "All" Then

In the above example I want an action when All is shown as the selected slicer item.

The following line needs to match the name of your slicer.

With ActiveWorkbook.SlicerCaches("Slicer_Data")

As shown above the name of the slicer can be gleaned from the slicer dialog.

The chart names need to be correct.

ActiveSheet.Shapes("Chart 1").ZOrder msoSendToBack

ActiveSheet.Shapes("Chart 2").ZOrder msoSendToBack

In the example my charts are called Chart 1 and chart 2.  Take note of your chart names in the name box (top left) when you click on your chart.

This is the display when All is clicked.

As the charts are overlayed perfectly the following is the result when South is selected in the slicer.

The Excel file attached shows the workings of this VBA slicer procedure.

Excel Slicer VBA.xlsm