Last month, I showed you my code that hides worksheets, based on the text in their names. Someone asked we could hide sheets based on tab color instead, so that’s how today’s example works.
Just select a sheet type from the drop down list, and any sheets with matching tab color are visible. All other sheets are hidden, except the Menu. Then, select “(All)” to see all the sheets again. Watch the video, and get the details, below.
Create a List of Sheet Types
The key step is to set up a list of sheet types, and use fill color that matches your sheets’ tab color. You’ll see the sheet type names in the drop down list on the Menu sheet.
Unfortunately, Excel has very limited formatting in its drop down lists – you can have any font and color you want, as long as it’s Tahoma black. So, use color names in your list, if you’d like to, or function names, or anything else that will help you remember.
Get the Setup Details
There is a video below, that shows how this code works, and explains how to set up your workbook. The video is about 13 minutes long, so I hope you don’t nod off, halfway through it! Maybe you can take a break halfway through it, and to help you jump to the sections of interest, I put the timeline below the video.
If you prefer written instructions, go to the Show Sheets With Tab Color page on my Contextures website. There are step by step instructions and screen shots, plus all the code.
There is also code that lists all the sheets in the workbook, and shows their tab color. That macro is also in the sample file, but isn’t covered in the video.
Download the Sample File
To test the macros, you download the sample file, from the Show Sheets With Tab Color page on my Contextures website. The workbook is in xlsm format, and contains macros. Be sure to enable macros when you open the file, if you want to see how the macros work.
NOTE: To hide sheets based on their names, download the sample file on this page: Show Specific Excel Sheets (Text)
Video: Hide Sheets Based on Tab Color
This video shows how the drop down list is set up, and shows how the macros work. There are instructions for setting up this technique in your own workbook. The timeline is below the video.
0:00 Introduction
0:47 How It Works
1:21 Named Ranges
1:46 Worksheet Macro
3:13 How to Set Up Your Own File
3:36 Set Up Your Sheets
4:21 Format the Sheet Type List
5:50 Name the Sheet Type List
6:31 Create the Drop Down List
7:20 Name the Cell
7:52 Add the Formulas
10:33 Name the Formula Cell
10:59 Add the Code
11:56 Test the Code
12:43 Get the Sample File
_______________________