Show Sheets by Tab Color

If you use tab colors to organize your worksheets, run this macro to quickly show specific sheets, based on the selected tab color. All other sheets are hidden. Select "(All)" to see all sheets again.

multi selection check box

Show Specific Sheets

Show Specific Tab Color

Instead of scrolling through all the sheet tabs, in a workbook with lots of worksheets, use a drop down list to show only the sheets with a specific tab color. When you make a selection, a macro runs, and hides any sheet that doesn't match the selected color.

You can download the sample file, to see how it works, and the setup details are shown below.

NOTE: To hide sheets based on their names, see this page: Show Specific Excel Sheets (Text)

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.

Select a Sheet Type

In the sample file, there is a Menu sheet that has a drop down list.

Click the arrow at the right of that cell, to see the list of sheet types
Then click on one of the options, such as Admin.
When you make a selection, a macro runs automatically, and hides all the sheets, except for the ones that have the tab color you selected. The Menu sheet is always visible too.

select a sheet type

See All the Sheets

To see all the sheets again, select the (All) option in the list of Sheet Types.

show all sheets

Drop Down List

The items in the drop down list are stored on the Admin_Lists sheet. Here are the details on using that list.

View the List of Sheet Types

To see the list of sheet types, go to the Admin_Lists sheet.

The sheet types are listed there, in a named Excel table.
The items in that table were selected (not the heading cell), and named as SheetTypes.

list of sheet types

Edit the List of Sheet Types

To add or edit an item in the list:

Type a new item at the end of the SheetTypes list, and the list will expand automatically to include the new item.
Sort the list A-Z, after adding new items – there is a bracket at the start of “ ALL”, so it should always sort to the top of the list.
Use a fill colour that matches the tab colour for that group of sheets.
Optionally, you can also change the font colour, so the text is easier to read. That does NOT affect which sheets are hidden.
Use any text that you want to, when describing the sheet type. Descriptive names are used in this example (Admin, Misc), but you could just use colour names instead (e.g. Blue, Green, None).

Menu Sheet Drop Down

On the Menu sheet, the drop down list was made with Data Validation, based on the SheetTypes named range. The cell with the drop down is named SelectType

named cell for sheet type

Selected Type Formula

There are two formulas on the Admin_Lists sheet, to find which sheet type was selected.

In cell F1, the formula is a simple link to the SelectedType cell on the Menu sheet.
- =SelectType
In cell H1 (named SelTypeNum), there is a MATCH formula, to find that sheet type in the SheetTypes list. It returns the position number.
- =IFERROR(MATCH(F1,SheetTypes,0),1)
If the MATCH function returns an error, because the sheet type is not found, the IFERROR function will return a 1. Since (All) is in position number 1, all the sheets will be visible.
The selected position number will be used in the macro that hides the sheets

formula for selected sheet type number

The Menu sheet code for this sample file is shown in the Menu Worksheet Code section, below

Multi-Select Option

There is another sample file in the download section, which has a check box to allow muli selection of categories.

If the Multi box is checked, additional sheets show, when you select a new category.

multi selection check box

There is also a (None) option in the drop down list - select that to hide all the sheets, except the Menu sheet.

The check box is linked to a named cell, SelMulti, which is on the Admin_Lists sheet.

multi selection check box

The Menu sheet code for this sample file is shown in the Menu Worksheet Code - Multi section, below

Menu Worksheet Code

On the Menu sheet, there is Worksheet_Change code, that runs when any change is made to the sheet. If the SelectType cell was changed, a macro runs.

First, all the sheets are made visible.
Then, if (All) was selected, or if the selection cell is blank (""), nothing else happens.
If any other sheet type was selected, the macro hides any sheet with a tab that does not match the selected colour. The Menu sheet is left visible too.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim ws As Worksheet
Dim wsAL As Worksheet
Dim wsM As Worksheet
Dim rngSel As Range
Dim c As Range
Dim rngList As Range
Dim rngNum As Range
Dim lColor As Long
Dim lColorI As Long
On Error GoTo errHandler
    
Set wsM = Me
Set rngSel = wsM.Range("SelectType")

If Target.Address = Range("SelectType").Address Then
  Application.EnableEvents = False
  Application.ScreenUpdating = False
  
  Set wsAL = Worksheets("Admin_Lists")
  Set rngList = wsAL.Range("SheetTypes")
  Set rngNum = wsAL.Range("SelTypeNum")
  
  'show all the sheets
  For Each ws In ThisWorkbook.Sheets
    ws.Visible = xlSheetVisible
  Next ws
      
  'find selected type and color in sheet list
  Set c = rngList.Cells(rngNum.Value, 1)
  lColor = c.Interior.Color
  lColorI = c.Interior.ColorIndex
  With rngSel
    .Interior.Color = lColor
    .Font.Color = c.Font.Color
  End With
  
  Select Case UCase(Target.Value)
    Case "(ALL)", ""
      'do nothing
    Case Else
      'hide sheets that don't match tab color
      ' and leave menu sheet visible
      For Each ws In ThisWorkbook.Sheets
        If ws.Name <> wsM.Name Then
          Select Case lColorI
            Case xlNone
              If ws.Tab.ColorIndex <> -4142 Then
                ws.Visible = xlSheetHidden
              End If
            Case Else
              If ws.Tab.ColorIndex = xlNone _
                Or ws.Tab.Color <> lColor Then
                  ws.Visible = xlSheetHidden
              End If
          End Select
        End If
      Next ws
  
  End Select
End If
    
exitHandler:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Exit Sub
errHandler:
  MsgBox "Could not show the sheets"
  GoTo exitHandler
End Sub

Menu Worksheet Code - Multi Select

On the Menu sheet, there is Worksheet_Change code, that runs when any change is made to the sheet. If the SelectType cell was changed, a macro runs.

Then, if (All) was selected, all sheets are made visible
If (None) was selected, all sheets are hidden, except the Menu sheet
If the selection cell is blank (""), nothing happens.
If any other sheet type was selected:
- If the Multi check box is checked:
  - the macro leaves any currently visible sheets showing
  - then it shows any sheet with a tab that match the latest selected colour
  - other sheets remain hidden, except the Menu
- If the Multi check box is NOT checked:
  - the macro shows any sheet with a tab that match the latest selected colour
  - other sheets are hidden, except the Menu

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim wsAL As Worksheet
Dim wsM As Worksheet
Dim rngSel As Range
Dim c As Range
Dim rngList As Range
Dim rngNum As Range
Dim lColor As Long
Dim lColorI As Long
Dim bMulti As Boolean
On Error GoTo errHandler
    
Set wsM = Me
Set rngSel = wsM.Range("SelectType")

If Target.Address = Range("SelectType").Address Then
  Application.EnableEvents = False
  Application.ScreenUpdating = False
  
  Set wsAL = Worksheets("Admin_Lists")
  Set rngList = wsAL.Range("SheetTypes")
  Set rngNum = wsAL.Range("SelTypeNum")
  bMulti = wsAL.Range("SelMulti").Value
  
  'find selected type and color in sheet list
  Set c = rngList.Cells(rngNum.Value, 1)
  lColor = c.Interior.Color
  lColorI = c.Interior.ColorIndex
  With rngSel
    .Interior.Color = lColor
    .Font.Color = c.Font.Color
  End With
  
    Select Case UCase(Target.Value)
      Case "(ALL)", ""
        For Each ws In ThisWorkbook.Sheets
          ws.Visible = xlSheetVisible
        Next ws
      Case "(NONE)", ""
        wsM.Visible = xlSheetVisible
        For Each ws In ThisWorkbook.Sheets
          If ws.Name <> wsM.Name Then
              ws.Visible = xlSheetHidden
          End If
        Next ws
      Case Else
        If bMulti = True Then
          'show sheets that match latest tab color
          For Each ws In ThisWorkbook.Sheets
            If ws.Name <> wsM.Name Then
              Select Case lColorI
                Case xlNone
                  If ws.Tab.ColorIndex = -4142 Then
                    ws.Visible = xlSheetVisible
                  End If
                Case Else
'                  Select Case ws.Tab.ColorIndex
'                    Case xlNone
'                      ws.Visible = xlSheetHidden
'                    Case Else
                      If ws.Tab.Color = lColor Then
                        ws.Visible = xlSheetVisible
                      End If
'                  End Select
              End Select
            End If
          Next ws
        Else
          For Each ws In ThisWorkbook.Sheets
            ws.Visible = xlSheetVisible
          Next ws
          'hide sheets that don't match tab color
          ' and leave menu sheet visible
          For Each ws In ThisWorkbook.Sheets
            If ws.Name <> wsM.Name Then
              Select Case lColorI
                Case xlNone
                  If ws.Tab.ColorIndex <> -4142 Then
                    ws.Visible = xlSheetHidden
                  End If
                Case Else
                  If ws.Tab.ColorIndex = xlNone _
                    Or ws.Tab.Color <> lColor Then
                      ws.Visible = xlSheetHidden
                  End If
              End Select
            End If
          Next ws
        End If
    End Select
End If
    
exitHandler:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Exit Sub
errHandler:
  MsgBox "Could not show the sheets"
  GoTo exitHandler
End Sub

List All Sheets

The Menu sheet also has a list of all the sheets in the workbook, with a link to each sheet, and its tab colour.

sheet list with tab colour

Above the list of sheets, there is a button that runs runs the ListSheetsTab macro, to update that list. The macro code is shown below.

sheet list macro button

To view or edit the code in the sample file:

Right-click the "List All Sheets" button
Click Assign Macro, then click Edit

About the List Sheets Code

The following code creates a list of sheets in the active workbook, and puts the list on the Menu sheet.

The list will start in row 4 (lRowHead = 4), and in column 5 (lCol = 5). You can change those numbers in the code, if you want the list in a different location.

When the code runs, it enters an item number and the sheet name.
Next, the ColorIndex property of the tab is checked.
- If that is -4142 (No Fill), then the sheet list cell shows No Fill as well.
- For all others, the Color property is used as a fill colour for the sheet list cell
Then a hyperlink to the sheet is created, in the second column of the list
Finally, after all the sheets have been added to the list, the heading row is bolded, and the columns are AutoFit.

Technical Notes:

The tab's Color property is used to fill the cells, because it gives accurate colours on its own.
However, the Color property returns 0 (zero) for Black, and False for No Fill.
Because False = 0, "No Fill" sheets would appear as black, in the list of sheets.
To prevent this error, the ColorIndex property is checked first, and applicable cells are formatted with No Fill.

Sub ListSheetsTab()
'downloaded from www.contextures.com
'list all sheets in active workbook
Dim wb As Workbook
Dim ws As Worksheet
Dim wsM As Worksheet
Dim lColorC As Long 'cell color
Dim lColorT As Long 'tab color
Dim lRow As Long
Dim lRowHead As Long
Dim lCol As Long
Dim lCols As Long
Dim lColEnd As Long
Dim lSh As Long
On Error Resume Next
Application.ScreenUpdating = False
Application.EnableEvents = False

Set wb = ActiveWorkbook
Set wsM = Worksheets("Menu")
lRowHead = 4
lCol = 5
lCols = 3
lColEnd = lCol + lCols - 1
lRow = lRowHead + 1

With wsM.Range(wsM.Cells(lRowHead, lCol), _
    wsM.Cells(lRowHead, lColEnd))
  .EntireColumn.Clear
  .Value = Array("ID", "Sheet", "Tab Color")
End With
      
With wsM
  For Each ws In wb.Worksheets
    .Range(.Cells(lRow, lCol), _
      .Cells(lRow, lColEnd - 1)).Value _
      = Array(lRow - lRowHead, ws.Name)
      If ws.Tab.ColorIndex = -4142 Then
        .Cells(lRow, lColEnd) _
          .Interior.ColorIndex _
          = ws.Tab.ColorIndex
      Else
        .Cells(lRow, lColEnd) _
          .Interior.Color _
          = ws.Tab.Color
      End If
      'add hyperlink to sheet name
      .Hyperlinks.Add _
          Anchor:=.Cells(lRow, lCol + 1), _
          Address:="", _
          SubAddress:="'" & ws.Name & "'!A1", _
          ScreenTip:=ws.Name, _
          TextToDisplay:=ws.Name
    lRow = lRow + 1
  Next ws
  
  With .Range(.Cells(lRowHead, lCol), _
      .Cells(lRowHead, lColEnd))
    .Font.Bold = True
    .EntireColumn.AutoFit
  End With
End With
 
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Get the Sample File

1. Single Selection: This is the original sample file, and when you select a category, only the sheet from that category remain visible, along with the Menu sheet. To test the macros, you can get the sample file with the code to show specific sheets in Excel, based on the selected tab color. 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.

2. Multi Selection: This sample file has a check box to allow muli selection of categories. If the Multi box is checked, additional sheets show, when you select a new category. To test the macros, you can get the sample file with the code to show specific sheets in Excel, based on the selected tab color. 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

3. Select Based on Sheet Names: To hide and show sheets based on their names, see the sample file on this page: Show Specific Excel Sheets (Text)