VBA: to filter a slicer via a cell value

[matteodalla]

Hi all,
I have a question for a topic which has been covered in a lot of threads, but still even with that help, it seems not to be working in my case.
The case is simple: I have a chart, connected to a slicer which is in turn connected to a pivot.
The pivot is not on the same tab where the slicer and the charts are (don't know if this matters actually).
The slicer is correctly connected to the pivot, meaning it works fine.
I want the value of cell AO14 to be the input to filter for the field "material" in the the slicer (slicer_material).
And I'd like the selected value of the slicer to be visible at the beginning/top of the slicer.
I tried with a lot of properties of slicer.cache and slice.items but I am stuck with this simple request.
Can somebody help me out?
Thanks in advance,

Matt

 

[matteodalla]

All,
Just to further clarify the request: the cell value which should trigger the filter for the slicer and the slicer itself are on the same tab (the pivot connected to the slicer in on another tab).
The request is filtering this slicer (slicer_material) with the value inputted in the cell AO14.

Any help with this simple code?
Thanks,
Matt

 

[Worf]

Hi Matt,

' sheet module
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sc As SlicerCache, si As SlicerItem
If Target.Address = "$K$43" Then                        ' cell to monitor
    Set sc = ActiveWorkbook.SlicerCaches("Volume")      ' desired slicer
    sc.ClearAllFilters
    For Each si In sc.SlicerItems
        If si.Caption = CStr(Target) Then
            si.Selected = True
        Else
            si.Selected = False
        End If
    Next
End If
End Sub

 

[matteodalla]

Hi Worf,
Thanks so much for your help.
I actually have another question.

I used your code to assign it to a button which reads the value inputted in the cell AO14.

Private Sub CommandButton2_Click()


Dim x As String
x = Range("AO14").Value


Dim sc As SlicerCache, si As SlicerItem

Set sc = ActiveWorkbook.SlicerCaches("Slicer_Material") ' desired slicer
sc.ClearAllFilters

For Each si In sc.SlicerItems
If si.Caption = x Then
si.Selected = True
Else
si.Selected = False
End If
Next


End Sub


However, this code literally passes all the material numbers I have (which are thousands).
Therefore in order to select one value it takes up to five minutes.

Is there a way to speed up this code without doing the check on each single value of the slicer?

Thanks in advance,
Matt

 

[Worf]

If you are using an OLAP data source, the VisibleSlicerItemsList property can be used.
The code below tells if this is the case:

Sub olap_or_not()
Dim sc As SlicerCache
Set sc = ActiveWorkbook.SlicerCaches(1)
MsgBox sc.OLAP
End Sub

 

[matteodalla]

If you are using an OLAP data source, the VisibleSlicerItemsList property can be used.
The code below tells if this is the case:

Sub olap_or_not()
Dim sc As SlicerCache
Set sc = ActiveWorkbook.SlicerCaches(1)
MsgBox sc.OLAP
End Sub

Dear Worf,
I am using OLAP data source. So, the next step would be?
Thank you!
M

 

[matteodalla]

Anyone any help?
I am simply trying to create a button to command a filter for a slicer with a value inputted in a cell (with OLAP data).
Thanks for any feedback!
Best,
Matteo

 

[Worf]

- I do not have an OLAP cube to test with, but it should be similar to the example below.
- You could turn the macro recorder on, manually select a slicer item and paste the resulting code here so I can look at it.

Sub Matteo()
Dim argv$
argv = "[Map].[Region].&[Europe]"                   ' this has to be adapted
ActiveWorkbook.SlicerCaches("Slicer_Region").VisibleSlicerItemsList = Array(argv)
End Sub

 

[matteodalla]

Hi Worf,
The problem is I want to assign this code to a button, so I don't know how to record a macro with a button involved.
Essentially this is what I'd like to do:

* I have a string in a cell
* the value of the string is saved in a variable
* I click the button which triggers the macro
* the macro should pass this variable to a slicer in order to filter it along with the variable value

I don't know if I am over-complicating it.
In the mean time I will keep trying to fix it.

Thanks in advance,
Best,
Matteo

 

[matteodalla]

And by the way if I record the simple act of filtering a slicer I got this (which is what I tried to code so far but it takes forever given the huge amount of materials I have)

With ActiveWorkbook.SlicerCaches("Slicer_Material")
.SlicerItems("100001772").Selected = True
.SlicerItems("100001773").Selected = False
.SlicerItems("100001907").Selected = False
.SlicerItems("100001917").Selected = False
.SlicerItems("100001918").Selected = False
.SlicerItems("100002065").Selected = False.....