<

Excel - Using a Sheet Tab as a Button for Expanding/Collapsing Supplementary Sheets.

Published on
36,194 Points
25,093 Views
16 Endorsements
Last Modified:
Awarded
Editor's Choice
There are too many sheets in this workbook
Many projects you may work on in Excel might develop the problem of having a huge number of sheets. Often, when the program is done, you find that many of these sheets are just used for driving the workbook, containing data tables, intermediary calculations, or advanced configuration parameters. Often, you don't want these showing up all the time, since the main program might be run using just one or two sheets. You could hide all the unnecessary sheets, but this can be cumbersome for developers, and can confuse advanced users. What you could really use, is an expand/collapse button.

Turning a Sheet Tab into an Expand/Collapse Button.
Check out what happens when you switch to the tab that says "Show My Guts" (expand): Tabs are CollapsedAnd now: Tabs are Expanded
Click the tab again (notice that the name has changed), and the expanded tabs collapse and become hidden again.

This idea is incredibly effective and surprisingly easy to implement. I've been using it for 5 years now and every client I show it to is delighted by the concept.

How to make your own
The steps are simple. All you need to do is create a new sheet that will serve only as a button tab, hide all the cells within it (to avoid a flickering effect), and add code to its 'Worksheet Activate' event that will show or hide a bunch of sheets, and then activate another sheet at the end.

To add the code, you'll need to open up the VBA Project Manager (Alt+F11). Next, in the list of sheets in the left pane, double click the sheet that you will be using as an expand/collapse tab. This will bring up a blank page for this sheet's code. You then copy the code below and paste it into that page. Make sure you edit the copied code so that the names of sheets refer to the sheets in your workbook. Now you can go back to the main Excel window and test it out.

Here is the code I use. It has been tested and confirmed to work in Excel 2000 upwards, and Excel 2004 on an Apple Mac. However, there is no Visual Basic for Applications (VBA) support in Excel 2008 for Mac, so this code cannot be used there.

Private Sub Worksheet_Activate()
    Dim sheet As Worksheet
    Application.ScreenUpdating = False
    If ShowHide.Name = "Show My Guts" Then
        'Make all sheets visible
        For Each sheet In ThisWorkbook.Sheets
            sheet.Visible = xlSheetVisible
        Next sheet
        'Change the sheet name to the "Collapse" name you want
        ShowHide.Name = "Hide My Guts"
        'Pick a sheet to display after the once hidden sheets are expanded
        Sheet4.Activate
    Else
        'Hide all sheets except the ones you want to keep visible
        For Each sheet In ThisWorkbook.Sheets
            If (sheet.Name <> Results.Name And sheet.Name <> Run.Name And sheet.Name <> ShowHide.Name) Then
               sheet.Visible = xlSheetVeryHidden
            End If
        Next sheet
        'Change the sheet name to the "Expand" name you want
        ShowHide.Name = "Show My Guts"
        'Pick a sheet to display after the sheets to be hidden are collapsed
        Run.Activate
    End If
    Application.ScreenUpdating = True
End Sub

Open in new window


Make it Your Own
See how simple it is? You can customize it as I have above, by changing what the tab is called. I called it "Show My Guts" and then "Hide My Guts", but you could be more professional and call it "Expand" and "Collapse", or something like that.

You can customize which sheets stay visible while the rest get hidden, or even just hard code the names of a few sheets to get hidden. You can have more than one of these tabs, for example, if your sheet has a bunch of tables, and a bunch of charts, you can have two tabs that say "Show/Hide Tables" and "Show/Hide Charts".

This is a very effective way to cut down on the excess sheets in your workbook and display only the relevant sheets for the current user - an essential component to good user interface design.

I've attached an example, but now that I've planted the idea in your head I'm sure you could find dozens of uses for a similar feature.

For instance, you could set a password dialogue to appear when the user clicks the sheet tab, and only expand to show advanced sheets if they have given the right password. Presto - privilege enabled applications in excel!

I hope you can find many great uses for this concept as I have.
Expand-Collapse-Tab-Example.xls
--
Alain Bryden
16
Author:alainbryden
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free