Excel Navigator List and Sort Sheets

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.

select a sheet from list

Introduction

Set up the Workbook

Add the Code to List and Sort Sheets

Modify the XML to Add the Buttons

Save the File as an Add-In

Install and Use the Add-In

Download the Workbook

Introduction

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:

Select Sheet -- click a sheet name, to go to that sheet
Sort the Sheets -- physically sorts the workbook's sheets in alphanumeric order
Select Hidden sheet -- lists all hidden and very hidden sheets -- select and unhide one
Hide ActiveSheet -- makes the active sheet hidden

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)

Set up the Workbook

First, you'll create a new workbook, to store the code that builds the Sheet Navigator Ribbon commands.

Create a new workbook.
Press Alt + F11, to open the Visual Basic Editor (VBE), where macros and user defined functions are stored.
In the VBE, press Ctrl + R, to open the Project Explorer
Find your new workbook in the list. For example, if your workbook is Book3, it will appear as VBAProject (Book3), as shown at right.
Right-click on your workbook name in the list, and choose Insert | Module
In the code window that appears on the right hand side, paste all the code that's shown below.

paste code in code window

Add the Code to List and Sort Sheets

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

Modify the XML to Add the Buttons

For instructions on adding the buttons to the Ribbon, see Ron de Bruin's web site, and add the following XML:

Ribbon XML

Save the File as an Add-In

First, you'll save the Sheet Navigator workbook as an Add-In, and make it accessible from other workbooks, as you work in Excel.

In the VBE, choose File | Close and Return to Microsoft Excel.
On the Excel Ribbon, click the Microsoft Office button, then click Save As.
Type a name for the file, e.g. NavToolbar
From the Save as type dropdown, choose Excel Add-In (*.xlam), at the bottom of the list.
Your AddIns directory will automatically be selected, e.g. C:\Documents and Settings\Contextures\Application Data\Microsoft\AddIns.
Click the Save button, to save the file.
Close Excel.

save as add-in

Install and Use the Add-In

Next, install the Add-In, so you can use it in any workbook.

Open Excel.
On the Excel Ribbon, click the Microsoft Office button, then click Excel Options.
In the Category list, click on Add-Ins.
From the Manage drop down, select Excel Add-ins, then click Go
Find your Add-In in the list, and add a check mark to its name.
Click OK to close the Add-Ins dialog box.

Add-Ins dialog box

Now you can use the add-in in any workbook.

The Sheet Navigator commands should appear at the far right of the Home tab on the Ribbon.
Click the Select a Sheet command, to view a list of sheets in the active workbook.
From the dropdown list, select a sheet, and that sheet will be activated.

select a sheet from list

You can add the Sheet Navigator commands to the Quick Access Toolbar (QAT):