I am trying to control the slicers of a pivot chart with Visual Basic. What I've done is to create a Listbox with the 12 months (in Excel, the slicer I have is for the 12 months too).
The problem I am having: when I start the userform, in Excel slicer there is always one option selected. So for example I choose in userform the last 3 months and it will show also January. (In excel, there is no way to deselect all)
This is the code I am using to select from Listbox and show in my userform chart:
Private Sub CommandButton1_Click() With ActiveWorkbook.SlicerCaches("Slicer_Month") For i = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(i) = True Then .SlicerItems(i + 1).Selected = True ElseIf ListBox1.Selected(i) = False Then .SlicerItems(i + 1).Selected = False End If Next i End With Set CurrentChart = Sheets("Sheet2").ChartObjects(1).Chart Fname = ThisWorkbook.Path & "\temp.gif" CurrentChart.Export Filename:=Fname, FilterName:="GIF" Image1.Picture = LoadPicture(Fname) End Sub
This is working good, except for the item which is selected by default.
I found this code (by Dominic) which SOLVES the problem of default selection:
Option Explicit Sub SelectMultipleSlicerItems() Dim oSlicerItem As SlicerItem Dim vItems As Variant Dim vMatchVal As Variant Application.ScreenUpdating = False vItems = Array("SER_Ft Myers_2015", "SER_Gainesville_2015", "SER_Miami_2015") With ActiveWorkbook.SlicerCaches("Slicer_Plan") .ClearManualFilter For Each oSlicerItem In .SlicerItems vMatchVal = Application.Match(oSlicerItem.Name, vItems, 0) If IsError(vMatchVal) Then oSlicerItem.Selected = False End If Next oSlicerItem End With Application.ScreenUpdating = True End Sub
This code is written for 3 items in slicers, but what I'm trying to do is to combine it with my listbox code.
Any help would be greatly appreciated,