excelvbapowerpivot

Application-defined or object-defined error with Excel Slicer

In a KPI dashboard on course attendance, I have a dropdown list with course names. A user picks a course and a macro must select the picked course in a slicer (connected to a cube).

When using the macro recorder, I see that the recorder uses a kind of index to pick up the course the user wants, and not the name (&[18] in my example):

ActiveWorkbook.SlicerCaches("Slicer_Course2").VisibleSlicerItemsList = Array("[Course].[Course].&[18]")

I want to browse through the different slicer items and only select the item which corresponds to the choice of the user in my dropdown list, but based on the name of the course. For example, if user chooses the course "Introduction To Slicer":

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

It bugs on ActiveWorkbook.SlicerCaches("Slicer_Courses2").SlicerItems.Count with message

application-defined of object-defined error

Solution

  • OK, I think that you can't get SlicerItems straightly from SlicerCaches according to this reference.

    So, you got error. Here my suggestion, try as follow:

    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