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 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.
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.
You can sort in sort in ascending order (Sort A-Z) or descending order (Sort Z-A).
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.
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.
Sharing is Caring
If you liked this please use the buttons below to share.