Quickly find specific sheets, by selecting a sheet type from a drop down list. Other sheets 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 specific sheets. All other sheets are hidden, except the Menu sheet, so you can quickly find what you need. Watch this video to see how it works, and the written instructions and sample file download link are below.
0:19 Select a Sheet Type
0:56 Set Up the List
1:55 Find a Command
2:17 View the VBA Code
3:29 View the Macro Code
4:59 Get the Sample File
On the Menu sheet, there is a data validation drop down list. Select a sheet type from the drop down list, and only the Menu sheet, and sheets with the selected text in their name are visible. For example, choose "computer" and all sheets with "computer" in their name are visible, along with the Menu sheet. All other sheets are hidden.
If you choose ALL as the Sheet Type, all the sheets in the workbook are made visible.
On another worksheet, there is a list of sheet types, formatted as a named Excel table. The data in that table was selected, and named as SheetTypes.
On the Menu sheet, the drop down list is based on that named range, and the cell with the drop down is named SelectType
You can add more items to the SheetTypes list, and the list will expand automatically to include them. Then, sort the list A-Z, after adding new items – there is a space character at the start of “ ALL”, so it will always sort to the top of the list.
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. If “ ALL” was selected, a macro runs to show all the sheets. If a different type was selected, it runs a macro to show only the sheets with that text in their name. The Menu sheet is left visible too.
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = Range("SelectType").Address Then Select Case Target.Value Case " All" ShowAllSheets Case "" 'do nothing Case Else ShowSelSheets 'do nothing End Select End If End Sub
The following macros are stored in a regular code module.
The ShowAllSheets macro runs when "ALL" is selected, and makes all the sheets visible.
Sub ShowAllSheets() Dim ws As Worksheet For Each ws In ActiveWorkbook.Sheets ws.Visible = xlSheetVisible Next ws End Sub
The ShowSelSheets macro runs if any other option is selected, and it shows only the Menu sheet, and any sheet that has the selected text in its name.
Sub ShowSelSheets() Dim ws As Worksheet Dim strType As String strType = Worksheets("Menu").Range("SelectType").Value For Each ws In ActiveWorkbook.Sheets If InStr(1, ws.Name, strType) > 0 Then ws.Visible = xlSheetVisible Else If ws.Name <> "Menu" Then ws.Visible = xlSheetHidden End If End If Next ws End Sub
Instead of creating separate macros to show all the sheets, and to show the selected sheets, you could use the following WorksheetChange procedure, submitted by Leonid Koyfman.
It has the following differences from the original code:
Private Sub Worksheet_Change(ByVal Target As Range) Dim ws As Worksheet Dim strType As String If Target.Address = Range("SelectType").Address Then strType = Target.Value If strType = "" Then Exit Sub End If For Each ws In ThisWorkbook.Sheets If InStr(1, " " & ws.Name & " ", " " _ & strType & " ", vbTextCompare) > 0 _ Or strType = "(All)" Then ws.Visible = xlSheetVisible Else If ws.Name <> "Menu" Then ws.Visible = xlSheetHidden End If End If Next ws End If End Sub
To test the macros, you can download the sample file with the code to show specific sheets in Excel.
Last updated: December 13, 2016 11:41 AM