Sometimes you may want to group worksheets within a workbook and display just that group.
You could, of course, split them into seperate workbooks to achieve this, but that
could sperate the information from the data or the code that produces the data.
This page will provide a code (and a complete workbook) that will demonstrate how to
accomplish displaying worksheets depending on the worksheet tab color.
A full working copy of a workbook that utilises this technique can be found
here
In order to implement this process you will need to have 2 Userforms and 2 Functions defined.
There will also be a requirement to code a statement for when the Workbook is initially opened.
All the required coding and userforms are available by following the appropriate links below.
The first thing to do is to import the userform Zip file that is linked to on the left.
Once it is downloaded and saved, extract the files ready for importing into your workbook.
Create the Required Worksheets
You will need at least two worksheets in your workbook. These must be named $$$Info &
$$$Control unless you change the code in the userforms.
$$$Info is used to provide instructions on how to use the
facility and provide a "default" worksheet in case no other worksheet meets the selection criteria.
The color of the $$$Info tab is irrelevant for the purpose of being the "default" worksheet.
$$$Control is used to hold the control table used to populate
the worksheet selection form. The table can be in any location but it must have the column heading
Color (case sensitive) to indicate the start of the table. The column immediately after this
heading is where the description will be picked up from.
Note: If you change any of the names you will need to reflect those
changes in the VBA code of the userforms.
Add the Required Functions
Public Function myTabColor As Integer MyTabColor = Application.Sheets(ActiveSheet.Name).Tab.ColorIndex End Function
There are 2 functions that need to be coded for the process to work. These are:
SheetCount & myTabColor
myTabColor is just one line and so it is shown to the left. This function
will return, as an integer, the color index number of the currently active sheet.
SheetCount is available for download from the link on the left. It is
used to count the number of hidden sheets to make sure that there is always one sheet visible.
Add Code To Show Userform When Workbook is Opened
Private Sub Workbook_Open() ListDrop.Show vbApplicationModal End Sub
In order that the form selection sheet is shown when the workbook opens you will need to
add the code alongside to the ThisWorkbook Excel Objects in the developer window.
Demonstration of Selecting Worksheets by Color
Alongside is a video demonstration of our free working example of selecting tabs based on
the color of the tabs.
The full working example of this workbook is available from
here
Usage Instructions
The technique as mentioned here relies of there being a correctly formatted control table on the
$$$Control worksheet. This table must have a column with the word Color in it.
The column can be anywhere on the worksheet and the column should be used to supply the ColorIndex number
to be used for a group of worksheets. The column next to the "Color" column should be used to
describe the worksheets.
An example of the control table is:
Once the control table is built you will need to reload the "Worksheet Selection Form". This is accomplished by
clicking on the "Refresh" button on the form. An example of the "Worksheet Selection Form" is:
If you close the "Worksheet Selection Form" another modal form will be displayed that will allow you
to restore it. This is the "Reshow Selection" form and it is small enough to not interfere with
the displayed worksheets. As it is a modal form it can be dragged and dropped to any location.
The "Reshow Selection" form looks like:
Just click on the form and the "Worksheet Selection Form" will be redisplayed.
That is all there is to it.
If you need any support or assistance with any of the code on this site or if you would like to contact us, please click here
