Hi -
I have a workbook with several sheets. Each sheet has one table. My code:
- Creates a slicer on each sheet, and
- Connects that slicer to the table on that sheet
The slicer values are dynamic. They're based on values entered by the user in a separate sheet. So, I cannot refer in my code to a specific value by its name, because I can't know the slicer value names at runtime.
What I'm trying to do is programmatically FILTER THE SLICER on the active sheet. I want to choose only the FIRST VALUE in that slicer (whatever it may be). I'm doing this not only for performance reasons but also because picking more than one slicer value would make no sense in my context.
I keep getting Error 1004 - Application-defined or object-defined error
Sub FilterTheNewSlicer Dim slcrC As SlicerCache Dim slcr As Slicer Dim sItem As SlicerItem Dim i As Integer Dim iItemCount As Integer ' Loop through slicer caches For Each slcrC In ActiveWorkbook.SlicerCaches ' Loop through the slicers For Each slcr In slcrC.Slicers ' Check if the slicer is on the active sheet If slcr.Shape.Parent Is ActiveSheet Then ' Check if the slicer is the EXCLUDE slicer If InStr(1, slcrC.Name, "Slicer_ART") > 0 Then ' This is the correct slicer. Let's filter it... iItemCount = slcrC.SlicerItems.Count ' THIS LINE BOMBS If iItemCount > 1 Then For i = 2 To iItemCount slcrC.SlicerItems(i).Selected = False ' AND THIS LINE BOMBS (if I hard code the bomb above so it works) Next End If End If End If Next slcr Next slcrC End Sub
There's nothing syntactically wrong with the code that I can see, and it works fine here in some quick tests. Any chance you can post a workbook where it fails?
I've uploaded the XLS. I had to ZIP it to get it small enough. I also removed several user forms, sheets, and modules not relevant to this problem. Any errors you may notice are likely the result of those deletions. Thanks for your help.
Ah, OK. You're using an OLAP data source (the data model) so you need to do this differently. In fact it's a little simpler as you don't need to loop, you just assign an array of the values you want selected to the VisibleSlicerItemsList property. You get the item by accessing the relevant SlicerCacheLevel (you only appear to have one per slicer here) so:
slcrC.VisibleSlicerItemsList = Array(slcrC.SlicerCacheLevels(1).SlicerItems(1).Name)
is all that's needed.
That worked perfectly.
Sub FilterTheNewSlicer()
' Declare variables for this procedure
Dim slcrC As SlicerCache
Dim slcr As Slicer
' Loop through slicer caches
For Each slcrC In ActiveWorkbook.SlicerCaches
' Loop through the slicers
For Each slcr In slcrC.Slicers
' Check if the slicer is on the active sheet
If slcr.Shape.Parent Is ActiveSheet Then
' Check if the slicer is the ART or TEAM slicer
If InStr(1, slcrC.Name, "Slicer_ART") > 0 Then
' Select the first ART only
slcrC.VisibleSlicerItemsList = Array(slcrC.SlicerCacheLevels(1).SlicerItems(1).Name)
ElseIf InStr(1, slcrC.Name, "Slicer_TEAM") > 0 Then
' Select the first TEAM only
slcrC.VisibleSlicerItemsList = Array(slcrC.SlicerCacheLevels(1).SlicerItems(1).Name)
End If 'ART or TeAM slicer
End If ' Slicer on active sheet
Next slcr
Next slcrC
End Sub
