Sliding Panel in Excel

--> Cool, Isn’t it?

The other day I used the sliding panel technique in a Dashboard and it turned out pretty well. It acted like a control panel for all settings and options of my Dashboard! Let me show you how to set this up in your spreadsheet

 

Let’s Set up the Spreadsheet

1. Assume that we will be making a panel out of Col A and Col B and Col C will act like a margin
2. Also note that I have reduced the width of  Col C to 2 points to make it look like a margin

 

Next let’s get that arrow

1. Let’s use the Shapes (in Insert Tab) to make a small arrow that will act like an intuitive button to open or close the panel
2. Also note that the arrow should change its direction depending on whether the panel is open or closed. We’ll take care of that in the VBA code
3. Also note that I have filled Col C with grey color

 

Naming the Arrow

1. Let’s name the arrow as “arrow”. This will help us fetch the object easily while writing the VBA code
2. The process of naming the objects is the same as naming the cells

 

Now comes the code

Sub SlidingPanel()
If Range('A:b').EntireColumn.Hidden = False Then
 ActiveSheet.Shapes.Range(Array('arrow')).Select
 Selection.ShapeRange.IncrementRotation 180
 Range('A:B').EntireColumn.Hidden = True
 Range('a1').Select
Else
 ActiveSheet.Shapes.Range(Array('arrow')).Select
 Selection.ShapeRange.IncrementRotation -180
 Range('A:B').EntireColumn.Hidden = False
 Range('a1').Select
End If
End Sub

Now copy this code and paste it in the VBA window. Follow the steps

1. Use the shortcut ALT + F11 to open the VBA window
2. From the Insert Menu (in the VBA window) click on Module
3. A blank module (place to write the code) will be inserted
4. Paste the code in that window and close the VBA window
5. Note that blue & bold part of the code is editable. You can change the columns to customize which ever columns you want in your panel

 

Link the arrow to the Macro

1. Right click on the arrow and go to Assign Macro
2. Select the Macro that you have just copied and pasted
3. And you are good to go!

 

Quick Tip : Application of Sliding Panel

Obviously we have created this panel to hide something that we may not need on the screen too often, something like a control panel. So I am assuming that you’ll be putting Slicers, Text, Form X Controls in here and we want those objects to adjust as per the panel.

To make the object adjust to the panel automatically follow the steps

1. Right click on the object (could be any object that is placed inside the panel)
2. Go to Size and Properties
3. Under Properties click – “Move and Size with Cells”
4. All the objects will be now be moving & re-sizing along Col A & Col B (which is our Panel)

Other Useful Macros

1. Un  hiding all sheets at once
2. Creating a Sheet Index in Excel
3. Automated your filter with Macro