none
How to hide a slicer?

    Question

  • Hi,

    I want to be able to totally hide the slicers in my sheet. The idea is that the user can select the slicer items they want to see and then hide the slicers afterwards in order to be able to view a clean final report.

    I don't want to hide items in the slicer, I actually want to hide the slicer box. I have tried grouping rows and also hiding them and also playing around with the Position settings (i.e. Move but don't size with cells... and others), but the Slicers just stay on the sheet and get pushed down.

    I could potentially set the height of the slicer to 0 height in VBA using the below code, but I'm wondering if I'm missing an easy option in Excel.

    Dim PT1 As PivotTable
    Dim slc1 As Slicer
    
        Set PT1 = Sheets("ALL").PivotTables("PVT_POS_BRANCH") '(1)
        Set slc1 = PT1.Slicers("POS_CT") '(1)
        slc1.Height = 100
    I could also move the Slicers to another sheet, but this is not ideal as I want the user to see the results refresh instantly, rather than switching between sheets.

    Thanks

    Tuesday, November 13, 2012 12:39 PM

Answers

  • Hi guys,

    I was hoping there was a way to do this without VBA, but I was able to put together a function to do this when the user clicks a button:

    Sub hideSlicers()
    Dim slc1, slc2, slc3, slc4 As Slicer
    Dim buttonText As String
    Dim slicerHeight As Integer
    
    buttonText = ActiveSheet.Shapes.Range(Array("Button 1")).TextFrame.Characters.Text
    
    If buttonText = "Hide Slicers" Then
        slicerHeight = 0
        ActiveSheet.Shapes.Range(Array("Button 1")).TextFrame.Characters.Text = "Show Slicers"
        ActiveSheet.Rows("1:13").Hidden = True
    Else
        slicerHeight = 180
        ActiveSheet.Shapes.Range(Array("Button 1")).TextFrame.Characters.Text = "Hide Slicers"
        ActiveSheet.Rows("1:13").Hidden = False
    End If
    
    Set slc1 = ActiveSheet.PivotTables("PVT_POS_BRANCH").Slicers("POS_CT")
    Set slc2 = ActiveSheet.PivotTables("PVT_POS_BRANCH").Slicers("POS_REG")
    Set slc3 = ActiveSheet.PivotTables("PVT_FULL_BAND").Slicers("FULL_CT")
    Set slc4 = ActiveSheet.PivotTables("PVT_FULL_BAND").Slicers("FULL_REG")
    
    slc1.Height = slicerHeight
    slc2.Height = slicerHeight
    slc3.Height = slicerHeight
    slc4.Height = slicerHeight
    
    Range("A1").Select
    
    End Sub
    Thanks for the ideas anyway

    • Marked as answer by UpsideDown55 Wednesday, November 14, 2012 10:12 AM
    Wednesday, November 14, 2012 10:10 AM

All replies

  • Hi UpsideDown55,

    You can also set their visible property to false?


    Regards, Jan Karel Pieterse|Excel MVP|http://www.jkp-ads.com
    Tuesday, November 13, 2012 1:12 PM
  • Hi,

    Just a question, if the slicer is hiden, how to make it show? You want to use a button or something else?

    Based on my test, I can do that using the code:

    Sub POS_CT_Click()

    Dim PT1 As PivotTable

    Dim slc1 As Slicer

    Set PT1 = Sheets("ALL").PivotTables("PVT_POS_BRANCH") '(1)

    Set slc1 = PT1.Slicers("POS_CT") '(1)

    slc1.Cut

    End Sub

    This code will be run when the user click the slicer.

    And the slicer will be cut. If you want it show again, just press Ctrl+V.


    Jaynet Zhang
    TechNet Community Support
    A new Office has arrived, try it now.
    A beautiful Start. It begins here. Windows 8 and Windows RT.
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, November 14, 2012 10:00 AM
    Moderator
  • Hi guys,

    I was hoping there was a way to do this without VBA, but I was able to put together a function to do this when the user clicks a button:

    Sub hideSlicers()
    Dim slc1, slc2, slc3, slc4 As Slicer
    Dim buttonText As String
    Dim slicerHeight As Integer
    
    buttonText = ActiveSheet.Shapes.Range(Array("Button 1")).TextFrame.Characters.Text
    
    If buttonText = "Hide Slicers" Then
        slicerHeight = 0
        ActiveSheet.Shapes.Range(Array("Button 1")).TextFrame.Characters.Text = "Show Slicers"
        ActiveSheet.Rows("1:13").Hidden = True
    Else
        slicerHeight = 180
        ActiveSheet.Shapes.Range(Array("Button 1")).TextFrame.Characters.Text = "Hide Slicers"
        ActiveSheet.Rows("1:13").Hidden = False
    End If
    
    Set slc1 = ActiveSheet.PivotTables("PVT_POS_BRANCH").Slicers("POS_CT")
    Set slc2 = ActiveSheet.PivotTables("PVT_POS_BRANCH").Slicers("POS_REG")
    Set slc3 = ActiveSheet.PivotTables("PVT_FULL_BAND").Slicers("FULL_CT")
    Set slc4 = ActiveSheet.PivotTables("PVT_FULL_BAND").Slicers("FULL_REG")
    
    slc1.Height = slicerHeight
    slc2.Height = slicerHeight
    slc3.Height = slicerHeight
    slc4.Height = slicerHeight
    
    Range("A1").Select
    
    End Sub
    Thanks for the ideas anyway

    • Marked as answer by UpsideDown55 Wednesday, November 14, 2012 10:12 AM
    Wednesday, November 14, 2012 10:10 AM
  • (It's been a while since this question was posted however it is worth responding just in case someone else has the same question down the road.)

     There is a much easier way that doesn't involve VBA. (I'm using Excel 2010)

    Go to the Home tab, Editing group, Find & Select, and click on Selection Pane. Click on the eye icon beside the slicer name.

    HTH - JoAnn


    -- JoAnn Paules 2004-2010 MVP Microsoft Publisher
    Tech Editor for "Using Microsoft Publisher 2010"


    • Edited by JoAnn Paules Thursday, May 16, 2013 11:21 AM
    • Proposed as answer by ShpendG Monday, October 14, 2013 10:05 AM
    Thursday, May 16, 2013 11:20 AM
  • I Agree with JoAnn,

    This is easier and effective !  Thanks for sharing JoAnn :)

    Cheers

    - KG

    Friday, August 30, 2013 4:09 PM
  • Abso-freaking brilliant.  Thanks, JoAnn.  :)
    Wednesday, September 04, 2013 4:52 PM
  • Hi JoAnn,

    Thanks so much for that. Based on your instructions, I recorded a macro of the same and got the following code. This would be useful if anyone wants to have a button the user can click on to hide all slicers:


    ActiveSheet.Shapes.Range(Array("POS_CT")).Visible = msoFalse  'TO HIDE THE SLICER
    ActiveSheet.Shapes.Range(Array("POS_CT")).Visible = msoTrue   'TO SHOW THE SLICER

    where POS_CT is the name of the slicer

    Wednesday, September 11, 2013 9:28 AM
  • Hello,

    I did the following :

    1) I have grouped 10 columns (Data - Group)

    2) Moved the slicers into the grouped columns area

    3) Selected all my slicers, went to Options and selected Group.

    4) added a VBA to expand or hide the grouped columns (the slicers disappear with the columns)

    Hope this works for you also.

    Br,

    Ionut

     

    Thursday, September 26, 2013 11:14 AM
  • I guess I'm old school. I would rather teach someone how to use the Selection Pane than fuss with a macro. Plus many of our files go on shared drives and that means adding more folders to the trusted locations.

    (I won't mention how no one else at work uses slicers other than me.)

    JoAnn


    -- JoAnn Paules 2004-2010 MVP Microsoft Publisher Tech Editor for "Using Microsoft Publisher 2010"

    Thursday, September 26, 2013 10:55 PM
  • Helpfull, Thank you JoAnn !

    Regards,

    ShpendG

    Monday, October 14, 2013 10:08 AM
  • Thank You so much for providing the most easiest and effective method.

    Monday, February 10, 2014 8:06 PM