CODEWITHSUNDEEP
excelvba
Christopher Weisbein
Christopher Weisbein

Reputation: 13

Slicer modifiying properties

Trying to use VBA to generate a pivot table and associated slicers. I am able to generate the table and slicers just fine, but I am getting error 5 when I am trying to modify the Slicer Properties. The error is in the "With" line. What do I currently have wrong?

Attempted to change the name values to the actual Slicer_ShipQuantity. And have looked around online, but I do not see any solutions. The one that I did find was from this website, https://www.microsoft.com/en-us/microsoft-365/blog/2011/01/27/control-slicers-by-using-vba/ however, this does not seem to work when I use it/type-copy it.

'This works to create slicer
ActiveWorkbook.SlicerCaches.Add2(ActiveSheet.PivotTables("MasterPivot"), "ShipQuantity") _
        .Slicers.Add ActiveSheet, , "ShipQuantity", "ShipQuantity", _
        RowLocation, ColumnLocation, width, Height

'this does not work to modify slicer settings. And I have no idea why.
With ActiveWorkbook.SlicerCaches("ShipQuantity").Slicers("ShipQuantity")
        .NumberOfColumns = 3
        .RowHeight = 13
        .ColumnWidth = 70
End With

Expecting to get have the code modify the displayed slicer, without having any errors appear. Please and Thanks!

Upvotes: 1

Views: 876

Answers (1)

Tim Williams
Tim Williams

Reputation: 166351

Slicers.Add returns a reference to the added slicer, so you could use that reference directly

https://learn.microsoft.com/en-us/office/vba/api/excel.slicers.add

Dim slcr

Set slcr = ActiveWorkbook.SlicerCaches.Add2(ActiveSheet.PivotTables("MasterPivot"), "ShipQuantity") _
        .Slicers.Add(ActiveSheet, , "ShipQuantity", "ShipQuantity", _
        RowLocation, ColumnLocation, width, Height)


With slcr
        .NumberOfColumns = 3
        .RowHeight = 13
        .ColumnWidth = 70
End With

Upvotes: 2

Related Questions