Select multiple items in Slicer

[rahulsahu910]

Hi,

I am trying to select multiple items in a given slicer, i recorded a macro to check how it happens and the result is below

Sub Macro1()
'
' Macro1 Macro
'


ActiveWorkbook.SlicerCaches("Slicer_costcentername1").VisibleSlicerItemsList = _
Array( _
"[costcentername].&[abc]", _
"[costcentername].&[def]", _
"[costcentername].&[ghi]", _
"[costcentername].&[jkl]")


End Sub

Tried to replicate the same thing as below

Sub Macro1()
'
' Macro1 Macro
'


Dim st As String
st = """_[costcentername].&[abc]""" + ",_" + """[costcentername].&[def]""" + ",_" + """[costcentername].&[ghi]" + ",_" + "[costcentername].&[jkl]"""
ActiveWorkbook.SlicerCaches("Slicer_costcentername1").VisibleSlicerItemsList = Array(st)

End Sub


But the above code selects all the options present in the slicer. Kindly help to resolve the issue.

 

[jkpieterse]

This should work:

Sub Macro1()
    Dim VisibleItems() As String
    ReDim VisibleItems(1 To 4)
    VisibleItems(1) = "[costcentername].&[abc]"
    VisibleItems(2) = "[costcentername].&[def]"
    VisibleItems(3) = "[costcentername].&[ghi]"
    VisibleItems(4) = "[costcentername].&[jkl]"
    ActiveWorkbook.SlicerCaches("Slicer_costcentername1").VisibleSlicerItemsList = VisibleItems
End Sub

 

[rahulsahu910]

This should work:

Sub Macro1()
    Dim VisibleItems() As String
    ReDim VisibleItems(1 To 4)
    VisibleItems(1) = "[costcentername].&[abc]"
    VisibleItems(2) = "[costcentername].&[def]"
    VisibleItems(3) = "[costcentername].&[ghi]"
    VisibleItems(4) = "[costcentername].&[jkl]"
    ActiveWorkbook.SlicerCaches("Slicer_costcentername1").VisibleSlicerItemsList = VisibleItems
End Sub

Thanks for the reply. But the visible items strings has be dynamic, how to achieve that

 

[rahulsahu910]

Also i am getting error using the above method.

Error 1004: The query did not run or the data model could not be accessed. "The restriction value provided by the consumer either does not match other restrictions or refers to an unknown object.

 

[jkpieterse]

Any way you can share an anonymised and stripped down version of your file?

 

[rahulsahu910]

Hi,

There is no option of attaching files here. However i can share a image and let you know the requirement.

The graph should change as per the multiple selections done in the slicer.

Thanks

 

[rahulsahu910]

tried inserting image but its not showing up in the post..

 

[jkpieterse]

You could upload something to OneDrive or Dropbox and share a link here...

 

[rahulsahu910]

https://drive.google.com/open?id=1nBjOVTnTCKDdahkIJHMXW-mZAZjEFuq7

 

[jkpieterse]

The sample file you uploaded contains a pivottable NOT tied to the data model whereas the VBA code you posted indicates a slicer tied to a pivot table which uses either the data model or an OLAP cube. The VBA syntax for slicers from these two source types is different. I recorded a macro, which then gives:

    With ActiveWorkbook.SlicerCaches("Slicer_Name")
        .SlicerItems("aaa").Selected = True
        .SlicerItems("bbb").Selected = True
        .SlicerItems("cccc").Selected = True
        .SlicerItems("ddd").Selected = False
        .SlicerItems("eee").Selected = False
        .SlicerItems("fff").Selected = False
        .SlicerItems("ggg").Selected = False
        .SlicerItems("hhh").Selected = False
        .SlicerItems("iii").Selected = False
    End With