Last updated: September 16, 2013 7:55 PM
Search Contextures Sites
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.
Download the zipped sample Excel 2003 sheet navigator file.
Click here for instructions for the Excel 2007 Sheet Navigator.
First, you'll create a new workbook, to store the code that builds the sheet navigator toolbar.
- 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.
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.
- In the VBE, choose File | Close and Return to Microsoft Excel.
- From the Excel menu bar, choose File | Save As.
- Type a name for the file, e.g. NavToolbar
- From the Save as type dropdown, choose Microsoft Office Excel Add-In (*.xla), 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.
Next, install the Add-In, so you can use the sheet navigator in any workbook.
- Open Excel.
- From the Excel menu bar, choose Tools | Add-Ins.
- Find your Add-In in the list, and add a check mark to its name.
- Click OK to close the Add-Ins dialog box.
- The myNavigator toolbar should appear, floating over the worksheet. You can drag it to another position, if you prefer.
- Click the Refresh Worksheet List button, to create a list of sheets in the active workbook.
- From the dropdown list, select a sheet, and that sheet will be activated.
- 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
Contextures Inc., Copyright ©2014
All rights reserved.