|
Home > Macros > Basics > Navigation Sheet Selector for Excel 2003Get 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
|
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.

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

Store the following code on Module1.
Sub Auto_Close()
On Error Resume Next
Application.CommandBars("MyNavigator").Delete
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
Application.CommandBars("MyNavigator").Delete
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
Else
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"
Else
wks.Select
End If
End Sub
'=================================================================
Sub RefreshTheSheets()
Dim ctrl As CommandBarControl
Dim wks
Set ctrl = Application.CommandBars("myNavigator") _
.FindControl(Tag:="__wksnames__")
ctrl.Clear
For Each wks In ActiveWorkbook.Sheets
If wks.Visible = xlSheetVisible Then
ctrl.AddItem wks.Name
End If
Next wks
End Sub
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 _
after:=Sheets(Sheets.Count)
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
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
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 the sheet navigator in any workbook.

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
![]()
Last updated: March 9, 2023 4:06 PM