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

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): And now:
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
                      

Select all 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