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.
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.
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
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
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)
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.
Last updated: December 12, 2016 4:03 PM