<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

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

Published on
34,199 Points
23,099 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
14 Comments
LVL 18

Expert Comment

by:WaterStreet
This is a really nice trick that I could have used years ago.   Got my vote
0
LVL 58

Expert Comment

by:tigermatt
Alain,

Congratulations! Your article has been awarded EE Approved status and received a "Yes, this helped vote" from me.

Thanks for your work and for this very interesting concept. It will prove very useful to me and many others.

tigermatt
Page Editor
0
LVL 93

Expert Comment

by:Patrick Matthews
Alain,

I just loved the originality of the idea--I've been kicking around Excel long enough that I've seen a lot of the tricks, and this one took me completely by surprise.  Love it!

Cheers,

Patrick
0
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

LVL 74

Expert Comment

by:Qlemo
Interesting idea.
Just one slight correction: The sentence "Often, you don't want want these showing up all the time" contains a double "want" ...
0
LVL 21

Author Comment

by:alainbryden
Thanks, all, for your comments. I'm glad to have put something original out there. Hopefully one of the admins can edit that typo.

Alain
0
LVL 9

Expert Comment

by:Suveer Patil
Excellent one....
very useful for me as my most of the work will be with excel
0
 

Administrative Comment

by:Netminder
Set to Editor's Choice. Congratulations, Alain!

Netminder
Site Admin
0
LVL 61

Expert Comment

by:Kevin Cross
Good job, Alain.
Thanks for the Article!

Voted yes above.
0

Expert Comment

by:RWayneH
I am curious if everyone has created a  password protect version of this solution and one that can be executed from CommandButton or hotkey instead the WorkSheet_Activiate.  From a designer prospective I have some workbooks that I designed that only allow one sheet visible at a time, all the rest are hidden, however there are times when I open a workbook in edit mode, to work on the file were this would be very useful (especially when working in 2003) and it would be nice to have a Ctrl key to hit that opens everything up so it can be worked on and another the hide them again.

It would sure beat hiding and unhiding sheet all the time.  Great solution!!  -R-
0
LVL 74

Expert Comment

by:Qlemo
The same code can be triggered by key stroke, just by writing a macro which calls the same code, and assigning keyboard shortcuts to that macro.
0

Expert Comment

by:hendrkle
Great, thank you for sharing, it will surely come in handy!
0

Expert Comment

by:Greg Rogers
OK...need help altering it for my specific workbook...can I get some help with that?
0

Expert Comment

by:Greg Rogers
I think I am taking that showhide tab I created called "DAILY+" and altF11 ing it.  Paste in your code...change the names where you have "show my guts" and "hide my guts" to my nomenclature..."DAILY+" and "DAILY-".  Then, I want to make it work for only certain tabs...not EACH Sheet in the Workbook.  So, how do I change it to specify working for only certain tabs and not all tabs?
0

Expert Comment

by:Nguyen Giang
What must i change if i only want the tab to hide certain worksheet like Sheet A, Sheet B, and Sheet C but keep Sheet D, Sheet E, and Sheet F visible.
Then on another tab i create another one that would collapse Sheet D, Sheet E, and Sheet F together.
0

Featured Post

Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month