CODEWITHSUNDEEP
excelexcel-2013vba
spudsy
spudsy

Reputation: 137

Selected Slicer

I'm trying to figure out a way to quickly format one or more data model-connected slicers (the number of columns, cell width/height, slicer width/height, etc), generally right after adding them to the workbook, but also on demand. I've been trying to figure out how to traverse the Selection object hierarchy, but I can't seem to figure out how to go from the Object/Rectangle I get from the Selection object to the selected Slicer object.

I see that I can go the other way pretty easily though, as in I can access the Shape object directly from the Slicer using ActiveWorkbook.SlicerCaches("Slicer_Test1").Slicers("Test").Shape

Is my only option to iterate all the Slicers in all the SlicerCaches until I match a Shape with the same name as the Selection (or match multiple names if I've selected more than one slicer)?

Does this make any sense? I figured this should be easier to understand, but I'm going in circles trying to research it.

Thanks for any pointers you can provide!

Edit: removed powerpivot tag because that seems irrelevant now.

Upvotes: 0

Views: 338

Answers (1)

Rory
Rory

Reputation: 34055

You don't need the Selection at all:

Dim slc                   As Slicer
Set slc = ActiveWorkbook.ActiveSlicer
If Not slc Is Nothing Then
    ' do stuff
End If

Upvotes: 1

Related Questions