Search Contextures Sites

 

Excel -- Navigation Toolbar for Workbook Sheets

Set up the Workbook  
Add the Toolbar Code  
Save the File as an Add-In  
Install and Use the 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 toolbar also includes a button that sorts the sheets in alphanumeric order.

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

Download the zipped sample file.

Click here for instructions for the Excel 2007 version of this code.


Set up the Workbook

First, you'll create a new workbook, to store the code that builds the 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.
 

 

 

Add the Toolbar Code

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
 


Save the File as an Add-In

First, you'll save the 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.

 

 

 


Install and Use the Add-In

Next, install the Add-In, so you can use it 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.

Note: If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm   

 

 
       Home     Excel Tips     Excel Files      Blog    Contact

RSS Feed

Privacy Policy

 

 

The Excel Store

 
Last updated: May 29, 2009 4:04 PM