This free add-in puts a new command on the Excel Ribbon (Excel 2007 and later), with a list of sheets in the current workbook -- makes it easier to navigate through a large file. Also see Excel 2003 Sheet Navigator, and Sheet Lister Add-in.
Thanks to Dave Peterson, who wrote this technique, and to Ron de Bruin, who adapted the code for the Excel Ribbon.
The Sheet Navigator includes the following commands:
Note: Select Sheet shows a popup list of sheets, for 16 or fewer sheets. If more than 16 sheets, it opens the Activate window (like right-clicking on scroll buttons at bottom left of Excel)
First, you'll create a new workbook, to store the code that builds the Sheet Navigator Ribbon commands.
Store the following code on Module1.
'Original code for Excel 2003 from Dave Peterson 'https://www.contextures.com/xlToolbar01.html 'Changed for Excel Ribbon versions 'by Ron de Bruin Sub SheetList_RDB(control As IRibbonControl) Dim ws Dim I As Long On Error Resume Next ActiveSheet.Select If Err.Number > 0 Then MsgBox "There is no workbook open" Err.Clear On Error GoTo 0 Else For Each ws In ActiveWorkbook.Sheets If ws.Visible = True Then I = I + 1 Next ws If I > 16 Then Application.CommandBars("Workbook Tabs") _ .Controls(16).Execute Else Application.CommandBars("Workbook Tabs") _ .ShowPopup End If End If End Sub Sub Hidden_SheetList_RDB(control As IRibbonControl) If ActiveWorkbook.ProtectStructure = True Then MsgBox "Please unprotect this workbook's " _ & vbCrLf _ & "structure and try again" Exit Sub End If On Error Resume Next Application.CommandBars("Ply") _ .FindControl(ID:=891).Execute On Error GoTo 0 End Sub Sub Hide_ActiveSheet_RDB(control As IRibbonControl) If ActiveWorkbook.ProtectStructure = True Then MsgBox "Please unprotect this workbook's " _ & vbCrLf _ & "structure and try again" Exit Sub End If On Error Resume Next Application.CommandBars("Ply") _ .FindControl(ID:=890).Execute On Error GoTo 0 End Sub
Insert another module, and store the following code on Module2.
'Original code for Excel 2003 from Dave Peterson 'https://www.contextures.com/xlToolbar01.html Sub DP_RDB_SortSheets(control As IRibbonControl) Dim CalcMode As Long Dim myArr() As String Dim sCtr As Long Dim iCtr As Long On Error Resume Next ActiveSheet.Select If Err.Number > 0 Then MsgBox "There is no workbook open" Err.Clear On Error GoTo 0 Else If ActiveWorkbook.ProtectStructure = True Then MsgBox "Please unprotect this workbook's " _ & vbCrLf _ & "structure and try again" Exit Sub End If Application.ScreenUpdating = False CalcMode = Application.Calculation Application.Calculation = xlCalculationManual ActiveSheet.DisplayPageBreaks = False ReDim myArr(1 To Sheets.Count) sCtr = 0 For iCtr = 1 To Sheets.Count If Sheets(iCtr).Visible = xlSheetVisible Then sCtr = sCtr + 1 myArr(sCtr) = LCase(CStr(Sheets(iCtr).Name)) End If Next iCtr ReDim Preserve myArr(1 To sCtr) ShellSort myArr For iCtr = 1 To sCtr Sheets(CStr(myArr(iCtr))).Move _ after:=Sheets(Sheets.Count) Next iCtr With Application .Calculation = CalcMode .ScreenUpdating = True End With End If End Sub Sub ShellSort(List As Variant, Optional ByVal LowIndex As Variant, _ Optional HiIndex As Variant) 'Translation of Shell's Sort as described in ' "Numerical Recipes in C", 2nd edition, Press et al. 'For large arrays, consider Quicksort. 'This algorithm is at least 'as good up to about 100 or so elements. 'But with 500 randomized 'elements it is about 27% slower than QSort, and looks 'increasingly worse as the array size increases. 'Dec 17, '98 - David J. Braden Dim I As Long, j As Long, inc As Long Dim var As Variant If IsMissing(LowIndex) Then LowIndex = LBound(List) If IsMissing(HiIndex) Then HiIndex = UBound(List) inc = 1 Do While inc <= HiIndex - LowIndex: inc = 3 * inc + 1: Loop Do inc = inc \ 3 For I = LowIndex + inc To HiIndex var = List(I) j = I Do While List(j - inc) > var List(j) = List(j - inc) j = j - inc If j <= inc Then Exit Do Loop List(j) = var Next Loop While inc > 1 End Sub
For instructions on adding the buttons to the Ribbon, see Ron de Bruin's web site, and add the following XML:
First, you'll save the Sheet Navigator workbook as an Add-In, and make it accessible from other workbooks, as you work in Excel.
Next, install the Add-In, so you can use it in any workbook.
Now you can use the add-in in any workbook.
You can add the Sheet Navigator commands to the Quick Access Toolbar (QAT):
Note: If you're new to macros, you may want to read David McRitchie's intro at: https://www.mvps.org/dmcritchie/excel/getstarted.htm
Download the zipped sample Excel Sheet Navigator file.
Last updated: April 14, 2021 4:03 PM