VBA code to hide and unhide all the Slicers in workbook.

[hananak]

Dear All,

I have a workbook with many sheets with multiple slicers in each sheet.

I am looking for a vba code that is assigned to a button, on clicking it it should hide all the slicers and again clicking it it should unhide.
The button text should change based on slicers current status (Hide/Unhide).

I would really appreciate, your help.

Thanks.

 

[Trevor G]

Have you named each slicer on each sheet? Are there any other shapes on the individual sheets other than slicers?

In VBA you could use an IF Statement where you would use an array and use each slicers name.

 

[John_w]

I don't think you can hide a slicer, however a simple workaround is to set its width and height to 0, effectively hiding it.

Code in a standard module:

Public Sub Hide_or_Unhide_Slicers()

    Dim clickedButton As Button
    Dim slCache As SlicerCache
    Dim sl As Slicer
    Dim slicerSettingsWs As Worksheet
    Dim r As Long
    
    Set slicerSettingsWs = ThisWorkbook.Worksheets("Slicer Settings")
    
    Set clickedButton = ActiveSheet.Buttons(Application.Caller)
    
    If clickedButton.Caption = "Hide" Then
    
        With slicerSettingsWs
            For r = 1 To .Cells(.Rows.Count, 1).End(xlUp).Row
                Set slCache = ThisWorkbook.SlicerCaches(.Cells(r, 1).Value)
                Set sl = slCache.Slicers(.Cells(r, 2).Value)
                sl.Width = 0
                sl.Height = 0
                'Select a cell on this slicer's worksheet to unselect the slicer itself
                sl.Parent.Select
                sl.Parent.Range("A1").Select
            Next
        End With
        clickedButton.Caption = "Unhide"
    
    ElseIf clickedButton.Caption = "Unhide" Then
    
        With slicerSettingsWs
            For r = 1 To .Cells(.Rows.Count, 1).End(xlUp).Row
                Set slCache = ThisWorkbook.SlicerCaches(.Cells(r, 1).Value)
                Set sl = slCache.Slicers(.Cells(r, 2).Value)
                sl.Width = .Cells(r, 3).Value
                sl.Height = .Cells(r, 4).Value
                'Select a cell on this slicer's worksheet to unselect the slicer itself
                sl.Parent.Select
                sl.Parent.Range("A1").Select
            Next
        End With
        clickedButton.Caption = "Hide"
    
    End If
    
End Sub


Public Sub Save_Slicers()

    Dim slicerSettingsWs As Worksheet
    Dim slCache As SlicerCache
    Dim i As Long, r As Long
    
    With ThisWorkbook
        Set slicerSettingsWs = Nothing
        On Error Resume Next
        Set slicerSettingsWs = .Worksheets("Slicer Settings")
        On Error GoTo 0
        If slicerSettingsWs Is Nothing Then
            Application.ScreenUpdating = False
            Set slicerSettingsWs = .Worksheets.Add(After:=.Worksheets(.Worksheets.Count))
            Application.ScreenUpdating = True
            slicerSettingsWs.Name = "Slicer Settings"
        End If
    End With
    slicerSettingsWs.Visible = xlSheetVeryHidden
    slicerSettingsWs.Cells.ClearContents
    
    r = 0
    For Each slCache In ThisWorkbook.SlicerCaches
        For i = 1 To slCache.Slicers.Count
            With slicerSettingsWs
                r = r + 1
                .Cells(r, 1).Value = slCache.Name
                .Cells(r, 2).Value = slCache.Slicers(i).Name
                .Cells(r, 3).Value = slCache.Slicers(i).Width
                .Cells(r, 4).Value = slCache.Slicers(i).Height
            End With
       Next
    Next

End Sub

First, run the Save_Slicers macro. This adds a hidden sheet, "Slicer Settings", if it doesn't exist, and stores the settings of all the slicers in the workbook. Storing the settings in a sheet means the slicers can be unhidden if you save the workbook with the slicers hidden and reopen it. You'll need to run Save_Slicers if you add or delete slicers.

Assign the Hide_or_Unhide_Slicers macro to your form control button, which has the caption "Hide". Clicking the button will hide the slicers and change to caption to "Unhide".

 

[Trevor G]

Hi John,

Yes you can Hide Slicers an example of the code I have just tested is shown below:

On my example sheet I have added 3 slicers = "Regions", "Sales" & "Products". If they have been set to filter the filter remains in place once the code has run. Kind of an extra question for the Operator is if they want to reset the filters.

Sub Button3_Click()
If ActiveSheet.Shapes.Range(Array("Regions", "Sales", "Products")).Visible = True Then
ActiveSheet.Shapes.Range(Array("Regions", "Sales", "Products")).Visible = False
Else
ActiveSheet.Shapes.Range(Array("Regions", "Sales", "Products")).Visible = True
End If
End Sub

I do like your idea about resizing as well.

 

[John_w]

Thanks Trevor, that's very helpful - I didn't know that a slicer is a Shape.

That makes the Hide_or_Unhide_Slicers code a lot simpler and the Save_Slicers code and associated "Slicer Settings" sheet isn't needed.

Public Sub Hide_or_Unhide_Slicers()

    Dim clickedButton As Button
    Dim ws As Worksheet
    Dim shp As Shape
   
    Set clickedButton = ActiveSheet.Buttons(Application.Caller)
   
    If clickedButton.Caption = "Hide" Then
   
        For Each ws In ThisWorkbook.Worksheets
            For Each shp In ws.Shapes
                If shp.Type = MsoShapeType.msoSlicer Then
                    shp.Visible = False
                End If
            Next
        Next
        clickedButton.Caption = "Unhide"
   
    ElseIf clickedButton.Caption = "Unhide" Then
   
        For Each ws In ThisWorkbook.Worksheets
            For Each shp In ws.Shapes
                If shp.Type = MsoShapeType.msoSlicer Then
                    shp.Visible = True
                End If
            Next
        Next
        clickedButton.Caption = "Hide"
   
    End If
   
End Sub

 

[Trevor G]

hi John, I found it helpful and I agree it does reduce the amount of coding.

 

[Domenic]

Here's another way, which loops through each slicercache within the workbook, and each slicer within each slicercache...

Sub HideUnhideAllWorkbookSlicers()

    Dim button As button
    Dim currentSlicerCache As SlicerCache
    Dim currentSlicer As Slicer
    
    Set button = ActiveSheet.Buttons(Application.Caller)
    
    For Each currentSlicerCache In ThisWorkbook.SlicerCaches
        For Each currentSlicer In currentSlicerCache.Slicers
            currentSlicer.Shape.Visible = Not (button.Caption = "Hide")
        Next currentSlicer
    Next currentSlicerCache
    
    If button.Caption = "Hide" Then
        button.Caption = "Unhide"
    Else
        button.Caption = "Hide"
    End If
    
End Sub

Hope this helps!

 

[Trevor G]

thank you Domenic for sharing. I like the method.

 

[Domenic]

These are all interesting methods, I hadn't thought of looping through each shape, and checking the type for msoSlicer, or using the width and height to hide the slicer, as @John_w has shown.

 

[hananak]

Dear All,

Thank you for providing various VBA codes. The problem is when I put these codes into VBA window. It gives me "Run Time Error 1004". Not sure what am I doing wrong. These are the steps I did.

1) First I created an active X control, edited the text and wrote hide and then I assigned the one of the above code to it. When I press the run button on the VBA window, it gives me the above error.

Please help me, I am a basic user of VBA in Excel.

Thanks.