Home > Macros > Basics > Navigation

Sheet Selector for Excel 2003

Get the free Sheet Navigator that makes it easy to find and activate a sheet in an Excel file. This is the Excel 2003 version. For other sheet listers, see Excel 2007 Sheet Navigator and Sheet Lister Add-in

save the sheet navigator workbook as an Add-In


Thanks to Dave Peterson, who wrote this technique.

If a workbook contains many sheets, it can be difficult to select the sheet that you want to use. The following technique creates a floating toolbar, that you can open in any workbook, and create a list of sheets in that workbook. The sheet navigator toolbar also includes a button that sorts the sheets in alphanumeric order.

Select a sheet from the dropdown list, to go to that sheet.

Select a sheet from the dropdown list

Set up the Sheet Navigator Workbook

First, you'll create a new workbook, to store the code that builds the sheet navigator toolbar.

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

Project Explorer

Add the Sheet Navigator Toolbar Code

Module 1 Code

Store the following code on Module1.

Sub Auto_Close()
    On Error Resume Next
    On Error GoTo 0
End Sub


Sub Auto_Open()
  'code written by Dave Peterson 2005-12-21
  'creates a toolbar with list of sheets in active workbook
  'and a SortSheets button

    Dim cb As CommandBar
    Dim ctrl As CommandBarControl

    On Error Resume Next
    On Error GoTo 0

    Set cb = Application.CommandBars.Add(Name:="myNavigator", temporary:=True)
    With cb
        .Visible = True
        Set ctrl = .Controls.Add(Type:=msoControlButton, temporary:=True)
        With ctrl
            .Style = msoButtonCaption
            .Caption = "Refresh Worksheet List"
            .OnAction = ThisWorkbook.Name & "!refreshthesheets"
        End With

        Set ctrl = .Controls.Add(Type:=msoControlComboBox, temporary:=True)
        With ctrl
            .Width = 300
            .AddItem "Click Refresh First"
            .OnAction = ThisWorkbook.Name & "!changethesheet"
            .Tag = "__wksnames__"
        End With
        Set ctrl = .Controls.Add(Type:=msoControlButton, temporary:=True)
        With ctrl
            .Style = msoButtonCaption

            .Caption = "Sort Sheets"
            .OnAction = "'" & ThisWorkbook.Name & "'!SortSheets"
        End With

    End With

End Sub


Sub ChangeTheSheet()

    Dim myWksName As String
    Dim wks 

    With Application.CommandBars.ActionControl
        If .ListIndex = 0 Then
            MsgBox "Please select an existing sheet"
            Exit Sub
            myWksName = .List(.ListIndex)
        End If
    End With

    Set wks = Nothing
    On Error Resume Next
    Set wks = Sheets(myWksName)
    On Error GoTo 0

    If wks Is Nothing Then
        Call RefreshTheSheets
        MsgBox "Please try again"
    End If

End Sub


Sub RefreshTheSheets()
    Dim ctrl As CommandBarControl
    Dim wks 

    Set ctrl = Application.CommandBars("myNavigator") _

    For Each wks In ActiveWorkbook.Sheets
        If wks.Visible = xlSheetVisible Then
            ctrl.AddItem wks.Name
        End If
    Next wks
End Sub

go to top

Code to Sort Sheets

Insert another module, and store the following code on Module2.

Sub SortSheets()

    Dim CalcMode As Long
    Dim myArr() As String
    Dim sCtr As Long
    Dim iCtr As Long
    If ActiveWorkbook.ProtectStructure = True Then
        MsgBox "Please unprotect this workbook's 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 _
    Next iCtr

    With Application
        .Calculation = CalcMode
        .ScreenUpdating = True
    End With
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
        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
            List(j) = var
    Loop While inc > 1

End Sub

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.

  1. In the VBE, choose File | Close and Return to Microsoft Excel.
  2. From the Excel menu bar, choose File | Save As.
  3. Type a name for the file, e.g. NavToolbar
  4. From the Save as type dropdown, choose Microsoft Office Excel Add-In (*.xla), at the bottom of the list.
  5. Your AddIns directory will automatically be selected, e.g. C:\Documents and Settings\Contextures\Application Data\Microsoft\AddIns.
  6. Click the Save button, to save the file.
  7. Close Excel. go to top

save the sheet navigator workbook as an Add-In

Install and Use the Add-In

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

  1. Open Excel.
  2. From the Excel menu bar, choose Tools | Add-Ins.
  3. Find your Add-In in the list, and add a check mark to its name.
  4. Click OK to close the Add-Ins dialog box.
  5. The myNavigator toolbar should appear, floating over the worksheet. You can drag it to another position, if you prefer.
  6. Click the Refresh Worksheet List button, to create a list of sheets in the active workbook.
  7. From the dropdown list, select a sheet, and that sheet will be activated.
  8. If you switch to a different workbook, refresh the list to see the sheet names for that workbook.

Install and Use the Add-In

Download the Sheet Navigator File

Download the zipped sample Excel 2003 sheet navigator file.

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    go to top

Related Tutorials

Sheet Lister Add-in

VBA Code, Copy to a workbook

VBA - Getting Started




Last updated: March 9, 2023 4:06 PM