Contextures

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.

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

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

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

Download the Sample File

To test the macros, you can download 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.

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

Get All the Excel News

For regular Excel news, tips, videos, and special offers, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.

Search Contextures Sites

 

pivot power premium

 

 

30 Excel Functions in 30 Days

 

 

 

pivot power premium

Last updated: December 12, 2016 4:03 PM

RSS