Deselect filter in slicers with VBA

ignasis · Jun 3rd 2016

Hello!

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:

Code

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

Display More

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:

Code

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

Display More

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,

Thanks!

ignasis · Jun 3rd 2016

Re: Deselect filter in slicers with VBA

Well, I managed to solve my problem just adding

Code

.clearManualFilter

So the final code:

Code

Private Sub CommandButton1_Click()


With ActiveWorkbook.SlicerCaches("SegmentaciónDeDatos_MES")
[B]    .ClearManualFilter[/B]




  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("Hoja2").ChartObjects(1).Chart
    Fname = ThisWorkbook.Path & "\temp.gif"
    CurrentChart.Export Filename:=Fname, FilterName:="GIF"
   Image1.Picture = LoadPicture(Fname)




End Sub

Display More

Quite simple!

Deselect filter in slicers with VBA

Participate now!

Share