You have a workbook with lots of sheets and you want to do things like hide sheets, delete some sheets or just sort them.
By using a very simple userform and some VBA we can do these things with a few clicks of our mouse.
The Form
The forms consist of one ListBox and four Command Buttons:
The List Box will display the list of sheets in the active workbook, and the buttons do exactly what they say.
Hiding and Unhiding Sheets
Rather than create a button to hide a sheet, I decided to make use of the _DblClick event. By using your mouse and double clicking on the name of a sheet, you hide it. Double click it again to unhide it.
Deleting Sheets
Click on a sheet name, then click the Delete button. You can select more than one sheet by holding down the CTRL key whilst clicking with your mouse.
Sorting Sheets
You can sort in ascending order (Sort A-Z) or descending order (Sort Z-A).
Extra Functionality
It's things like this make your life that bit easier and is exactly what VBA is built for.
This is a simple example of what you can do with a form and only scratches the surface of what is possible.
If you want to really see some cool stuff like :
- Searching for worksheets and navigating to them.
- Copying and renaming sheets.
- Protecting and unprotecting sheets.
- Moving, organizing, and coloring sheets.
- Flipping back and forth between two sheets.
- Jump to the first or last sheet.
- Moving sheets up, down, first, or last.
- See how many worksheets are in a workbook?
- See how many hidden sheets there are.
- Count how many sheets have a particular tab color.
Then you should check out the excellent Tab Hound which is made by Jon Acampora over at Excel Campus.
The Code
Enter your email address below to download the sample workbook.
If you want to look at my code and have a play around with it, you can download the workbook with the sample code.
Put the code (the form module and the code module) into your PERSONAL.XLSB so you can run it on any workbook.
Then you could add an icon to the QAT so you can run the code with a click of your mouse.
Hi guys
Many thanks for the code and form. Extremely useful.
However, could you please adivse how I could change the code (or add) so I could be able to hide some worksheets so they wouldn’t show on the list? I’ve tried it using the Case Function but wasn’t very successful.
Thank you
Hi Arnaldo,
If you change the sheet visibility so that it is xlSheetVeryHidden (in the VBA editor) then you can use this modified Sub to only list sheets that are not Very Hidden
Sub PopulateList() Dim counter As Long Dim HiddenPrefix As String SheetList.Clear For counter = 1 To Sheets.count If Sheets(counter).Visible <> xlSheetVeryHidden Then If Sheets(counter).Visible = False Then HiddenPrefix = "H" Else HiddenPrefix = "" End If SheetList.AddItem HiddenPrefix & vbTab & Sheets(counter).Name End If Next counter End SubHi
This code is fantastic for doing exactly what I wanted.
I did however modify this slightly so I can select the sheets for hiding and unhiding (the same ways as deleting) in addition to the double click option.
Also added a Yes/No check when deleting sheets with a message are you sure?
Thanks for sharing this code
Thanks Stuart, glad you found it useful
Phil,
Thanks for putting this tool together. It makes things much easier when managing a large workbook with several hidden sheets. I have added it to my Personal.xlsb with a QAT icon and have already used it quite often.
After playing around with it some, I added a feature that may be of interest to you or others – the ability to see/make “Very Hidden” sheets. As I’m sure you’re aware, there are three Visible properties of sheets. Most people use only the Hidden or Visible properties, but the Very Hidden property will hide a sheet in such a way that it doesn’t show up when right-clicking to unhide sheets. This can be handy when trying to prevent users from unhiding a critical sheet and making changes you didn’t intend.
The tweak I made to the PopulateList code was to use a Select Case statement on the Sheets.Visible property where Case 0 is Hidden (so returns “H”), Case 2 is Very Hidden (so returns “V”), and Case Else assumes Visible (so returns “”). I then copied the Delete button code and adjusted it to make a sheet VeryHidden so that you can select any sheet and click the button to make it Very Hidden.
Thanks again for this post!
Excellent work Ben. It’s great to hear what you have done and this is exactly why I do this. So people like yourself can take my code and tweak/enhance it to suit their own needs and add extra bits of functionality.
Good job.
Phil
Ben,
Would you be so kind to share this modified code by any chance?
Thanks,
Justin
Excellent tip and and tool. A couple of tweaks I’d like to see.
Column headings in the form: Hidden, Sheet name, Description(?)
I would like to see the option to add a hidden tab with longer descriptions / comments about the sheet. If a predefined tab name for the comments is found, then a lookup is performed on the tab names to find descriptions.
Please add a link to tutorial on how to copy this userform and all of it’s associated code to the PERSONAL.XLSB (assuming they don’t have one, like me. I saw your tutorial on creating an PERSONAL, but I don’t see how to move the userform to it. Is that an export, import process?
PS: add a note to this article reminding people that they could also create a button on their QAT to trigger this form, rather than the button you use in your example. This is handier when sharing the code with other forms where there may not be an appropriate place to put the button. After downloading your file and adding the macro button to the QAT I was able to use the form in other spreadsheets (of course it opens your example sheet in the process, oops).
I presume, adding the macro from the PERSONAL.XLSB would not launch the sheet?
Hi Ron,
All good suggestions. I didn’t include headings because it seemed clear to be what the sheet name was, but you can of course alter the form and quickly add a couple of labels in the right place.
A hidden sheet with descriptions for each sheet would also be a feature, but would complicate the coding somewhat.
I will write a separate tutorial on moving code into the PERSONAL.XLSB. You can export/import the code and form modules, or just click on the module in my workbook and drag it into PERSONAL.XLSB, as shown in the animated image towards the bottom of this post Excel progress bar in VBA
Yes, adding an icon on the QAT is the way to go, and I added a link to my article on doing just that. I overlooked that when I wrote the blog!
And yes, once the code and form are in PERSONAL.XLSB, running the code won’t open my workbook.
Thanks
Phil
Great utility Phil. It’s amazing how we can greatly enhance Excel’s functionality with a little bit of code. That’s why I love it. 🙂
I appreciate you mentioning Tab Hound as well. I use it everyday to search for and jump to sheets in the workbook quickly. It’s like Google Search for worksheets. 🙂
Hi John,
No worries, Tab Hound is excellent.
Cheers
Phil