Home > Macros > Basics > Worksheets Show Sheets by Tab ColorIf 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. |
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.
In the sample file, there is a Menu sheet that has a drop down list.
To see all the sheets again, select the (All) option in the list of Sheet Types.
The items in the drop down list are stored on the Admin_Lists sheet. Here are the details on using that list.
To see the list of sheet types, go to the Admin_Lists sheet.
To add or edit an item in the list:
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
There are two formulas on the Admin_Lists sheet, to find which sheet type was selected.
The Menu sheet code for this sample file is shown in the Menu Worksheet Code section, below
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.
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.
The Menu sheet code for this sample file is shown in the Menu Worksheet Code - Multi section, below
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.
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
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.
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
The Menu sheet also has a list of all the sheets in the workbook, with a link to each sheet, and its 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.
To view or edit the code in the sample file:
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.
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
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)
Last updated: March 5, 2023 3:10 PM