Changing properties on all slicers in workbook

[Costas]

Hi Chandoo,

I've set up a large excel table (23 x 32000) and created many pivot tables and pivot charts on separate sheets. Each pivot table or chart has 5-6 slicers link to it.

I'm trying to create a macro in Personal.xlsb to set the same properties in each slicer by loop through all of them in the active workbook.
I'm getting confused between slicers and slicer caches and although my macro will run, some of the properties are not changing.
As far as I understand, some of the changes are done at worksheet level and others at workbook level so I've created two types of loops.
I've added the On Error Resume Next to skip making changes to pivot charts but I'm sure if there is another way to do it.
I've marked below the changes that are not working.

Public MySheet As Worksheet
Public MyShape As Shape
Public MySlicer As Slicer
Public MySlicerCache As SlicerCache
Public MyPivot As PivotTable

Sub Shapes_and_Slicers()
  On Error Resume Next
        For Each MySheet In ActiveWorkbook.Sheets
            For Each MyShape In MySheet.Shapes
                MyShape.DisplayHeader = True 'NOT WORKING
                MyShape.Placement = xlFreeFloating 
                MyShape.DisableMoveResizeUI = True  'NOT WORKING
            Next MyShape
        Next MySheet

        For each MySlicerCache in ActiveWorkbook.SlicerCaches
               MySlicerCache .SlicerCache.CrossFilterType = xlSlicerCrossFilterHideButtonsWithNoData 'NOT WORKING
               MySlicerCache .SlicerCache.ShowAllItems = False 'NOT WORKING
        Next MySlicerCache
  On Error GoTo 0
End Sub

I've also tried combining MyPivot and MySlicer but joy either.

Thanks
Costas

 

[Debaser]

I think you could use:

Sub Shapes_and_Slicers()
    On Error Resume Next
    DIm MySlicerCache As SlicerCache
    For each MySlicerCache in ActiveWorkbook.SlicerCaches
           With MySlicerCache
                 .CrossFilterType = xlSlicerCrossFilterHideButtonsWithNoData
                 .ShowAllItems = False
                 Dim MySlicer As Slicer
                 For Each MySlicer In .slicers
                     MySlicer.DisplayHeader = True 'NOT WORKING
                     MySlicer.shape.Placement = xlFreeFloating 
                     MySlicer.DisableMoveResizeUI = True  'NOT WORKING
                 Next MySlicer
           End With
    Next MySlicerCache
End Sub

 

[Costas]

Prefect! Thanks Debaser!