User defined or object error with Excel Slicer
In the course attendance KPI dashboard, I have a drop-down list with course names. The user selects a course and the macro has to select the selected course in the slicer (associated with the cube).
When using a macro recorder, I see that the recorder is using some sort of index to select the course the user wants, not the name (& [18] in my example):
ActiveWorkbook.SlicerCaches("Slicer_Course2").VisibleSlicerItemsList = Array("[Course].[Course].&[18]")
I want to view various slicer items and only select the item that matches the user's selection in my dropdown, but based on the course title. For example, if a user chooses an Introduction to Slicer course:
Sub TestSclicer()
Dim i
ActiveWorkbook.SlicerCaches("Slicer_Courses2").ClearManualFilter
For i = 1 To ActiveWorkbook.SlicerCaches("Slicer_Courses2").SlicerItems.Count
If ActiveWorkbook.SlicerCaches("Slicer_Courses2").SlicerItems(i).Name = "Introduction To Slicer" Then
ActiveWorkbook.SlicerCaches("Slicer_Courses2").SlicerItems(i).Selected = True
Else
ActiveWorkbook.SlicerCaches("Slicer_Courses2").SlicerItems(i).Selected = False
End If
Next
End Sub
Error ActiveWorkbook.SlicerCaches("Slicer_Courses2").SlicerItems.Count
with message
application-defined of object-defined error
source to share
Ok I think you cannot directly get SlicerItems from SlicerCaches according to this link .
So you got the error. Here is my suggestion, try this:
Sub TestSclicer()
Dim sC As SlicerCache
Dim sI As SlicerItem
Dim index As Integer
Set sC = ActiveWorkbook.SlicerCaches("Slicer_Courses2")
sC.ClearManualFilter
For index = 1 To sC.SlicerCacheLevels.count
For Each sI In sC.SlicerCacheLevels(index).SlicerItems
If sI.Name = "Introduction To Slicer" Then
sI.Selected = True
Else
sI.Selected = False
End If
Next sI
Next index
End Sub
source to share