I’ve been toying with the idea of an animated Slide-Out Menu in Excel. As you can see, a Slide-Out Menu is simply a macro-driven way to hide your slicers away, until you need them.
As sexy as this animation effect is, it’s far from perfect.
The screen flicker ruins the illusion at times. And although you can adjust how the column slides open and closed (see the comments in the macro below), Excel sometimes ignores the code and choose its own width to account for the size of the slicer within column A. You really have to play around with the positioning of the slicer and the macro parameters to make it all mesh correctly.
All that being said, it’s still a spicy trick – and it’s just so much fun to experiment with.
This bit of magic is relatively easy to implement yourself. You can follow these general steps:
1. Place a Slicer in column A and set it to “Move and Size with Cells”
2. Place a small icon/shape in the upper left hand corner of column A
3. Copy and Paste the HideSlicer macro shown below into a standard Module.
4. Assign your newly pasted macro to the icon/shape added.
- Sub HideSlicer()
- Dim ColumnRange As String
- Dim SmallestSize As Double
- Dim LargestSize As Double
- Dim Increment As Double
- Dim FromSize As Double
- Dim ToSize As Double
- Dim Stepper As Double
- Dim i As Double
- ColumnRange = "A" '< --Change column to point to your columns
- SmallestSize = 8 '<--Adjust to the smallest column width needed
- LargestSize = 32 '<--Adjust to the largest column width needed
- Increment = 6 '<--Adjust to set the animation slower or faster
- Select Case ActiveSheet.Columns(ColumnRange).ColumnWidth
- Case Is <= SmallestSize
- FromSize = SmallestSize
- ToSize = LargestSize
- Stepper = Increment
- Case Is > SmallestSize
- FromSize = LargestSize
- ToSize = SmallestSize
- Stepper = -Increment
- End Select
- For i = FromSize To ToSize Step Stepper
- ActiveSheet.Columns(ColumnRange).ColumnWidth = i
- i = i
- Next i
- End Sub
If you want to save a bit of time, you can download a working example from the link below. If you like the look and feel of the sample dashboard, check out my Dashboard Tools Add-in. It gives you a quick and easy way to create infographic-style dashboards like the one demonstrated here.
Feel free to comment and suggest improvements to the macro or workbook.