Creating a Slide-Out Menu in Excel

 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.

  1. Sub HideSlicer()
  2. Dim ColumnRange As String
  3. Dim SmallestSize As Double
  4. Dim LargestSize As Double
  5. Dim Increment As Double
  6. Dim FromSize As Double
  7. Dim ToSize As Double
  8. Dim Stepper As Double
  9. Dim i As Double
  10.  
  11. ColumnRange = "A"  '< --Change column to point to your columns
  12. SmallestSize = 8   '<--Adjust to the smallest column width needed
  13. LargestSize = 32   '<--Adjust to the largest column width needed
  14. Increment = 6      '<--Adjust to set the animation slower or faster
  15.  
  16. Select Case ActiveSheet.Columns(ColumnRange).ColumnWidth
  17.     Case Is <= SmallestSize
  18.         FromSize = SmallestSize
  19.         ToSize = LargestSize
  20.         Stepper = Increment
  21.    
  22.     Case Is > SmallestSize
  23.         FromSize = LargestSize
  24.         ToSize = SmallestSize
  25.         Stepper = -Increment
  26. End Select
  27.  
  28. For i = FromSize To ToSize Step Stepper
  29.     ActiveSheet.Columns(ColumnRange).ColumnWidth = i
  30.     i = i
  31.     DoEvents
  32. Next i
  33. 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.

DashboardToolsLogo

.

Download the Slide-Out Menu Sample File

Feel free to comment and suggest improvements to the macro or workbook.

Happy Dashboarding!

17 thoughts on “Creating a Slide-Out Menu in Excel

  1. Tim Rodman

    Great trick! This kind of thing will be even more useful when the Excel Web App eventually supports VBA. Then we can fool all the executives into thinking that they have some really expensive/fancy dashboard software.

  2. Bob Phillips

    What are you complaining about Mike, the screen flicker is totally compatible with 2013 animation.

  3. Jon Acampora

    Awesome Mike! This technique could be used for a lot of different applications.

    For the 2013 version you could change the menu icon to a paperclip with eyeballs. That should eliminate the need to explain any animation hiccups… 🙂

  4. ruve1k

    Mike, why not just keep the slicers in a separate column? This would allow for a cleaner solution. SmallestSize would always be zero. This approach would probably not introduce the issues you experienced with slicer positioning causing Excel to ignore the specified column widths.

  5. Ezequiel

    Impressive.
    I will continue to amaze (stealing) my bosses.

    Congratulations.

    PD: Sorry for my poor English.

  6. Brij Arora

    Great Trick,

    I think for non VBA approach we can use group function from data tab to achive the same through grouping only single column. Yes we lose the smooth transition but worth of try to acheive the same in single click.

    What’s your view

  7. Doris Choo

    I like the color coded highlights. Makes the codes not only elegant, also easy to read and crisp. Nicey.
    Thanks.

  8. David B

    You should be able to get the animation to seem smoother by using smaller increments in the beginning and the end. Have a variabel stepper depending on where you are in the range of smallest to largest.

    That trick is used in all other animation and should work here. Its part of the 12 principles.

  9. AlexJ

    Mike,
    As ‘cool’ as this trick is, I waould like to use the functions of a panel like many other embedded office functions. Are you aware if this might become available in future with VBA control?

  10. datapig Post author

    AlexJ: I’m not sure. Given the aggresive moves toward using Excel in the multi-platform cloud, I doubt Microsoft will be keen on adding very many new VBA hooks.

    Jordan: Ahhh…we all have those mutant test files that didn’t work out. I just delete my failures never to speak of them again : )

  11. Wes

    I had some trouble with this one. I adjusted the column widths and it started always collapsing instead of expanding and then collapsing. I found that you have to choose increment, min, and max such that the difference between the minimum and maximum column widths is evenly divisible by the increment amount. Maybe there’s a better way to do that, but it worked for me. Thanks!

  12. Lindsay

    Hi there – this is brilliant. Thanks you!

    Upon implementing this, mine seems to work ok-ish the first couple of times, and then expands and contracts immediately when I click the macro-assigned button.

    I have kept the VBA code as is, barring a change or two to the column size specifications.

    Any idea why this could be happening?

    Thanks heaps!

    Kind regards,
    Lindsay

  13. Lindsay

    With regard to my above comment: I think I may have found the problem (but not yet the solution): When I click the shape to slide away the column/slicers, it doesn’t minimize the column to the minimum size, but rather t o some default setting of 9.14pts. Hence, the next time I run it, it picks up that the column width is greater than them minimum, and then slides it straight back midway through expanding. Hmm. Why could this be?

    Thanks!

  14. Lindsay

    It’s me again. Seem to have eventually fixed it: There is some weird relationship between the smallest size, largest size and increment. Only by tweaking them individually could I find a combination that works. Not sure why.

    Also, I rolled this over to a horizontal slide-away – adjust columns to rows, and column width to row height .Works a treat. Thanks for a springboard into greatness!

  15. Pingback: Creating a Slide-Out Menu – Nigel Learns To Blog

Leave a Reply

Your email address will not be published. Required fields are marked *