Microsoft Excel

Create an Index of Sheets in Your Workbook

If you've spent much time in a workbook with many worksheets, you know how painful it can be to find a particular worksheet. An index sheet available to every worksheet is a navigational must-have.

Using an index sheet will enable you to quickly and easily navigate throughout your workbook so that with one click of the mouse, you will be taken exactly where you want to go, without fuss. You can create an index in a couple of ways.

You might be tempted to simply create the index by hand. Create a new worksheet, call it Index or the like, enter a list of all your worksheet's names, and hyperlink each to the appropriate sheet by selecting Insert » Hyperlink... or by pressing Ctrl/figs/command.gif-K. Although this method is probably sufficient for limited instances in which you don't have too many sheets and they won't change often, you'll be stuck maintaining your index by hand.

The following code will automatically create a clickable, hyperlinked index of all the sheets you have in the workbook. The index is re-created each time the sheet that houses the code is activated.

This code should live in the private module for the Sheet object. Insert a new worksheet into your workbook and name it something appropriate-Index, for instance. Right-click the index sheet's tab and select View Code from the context menu. Enter the following Visual Basic code (Tools » Macro » Visual Basic Editor or Alt/Option-F11):

Private Sub Worksheet_Activate( )
Dim wSheet As Worksheet
Dim l As Long
l = 1
    With Me
        .Cells(1, 1) = "INDEX"
        .Cells(1, 1).Name = "Index"
    End With
    For Each wSheet In Worksheets
     If wSheet.Name <> Me.Name Then
      l = l + 1
      With wSheet
          .Range("A1").Name = "Start" & wSheet.Index
          .Hyperlinks.Add Anchor:=.Range("A1"), Address:="", SubAddress:= _
            "Index", TextToDisplay:="Back to Index"
      End With
         Me.Hyperlinks.Add Anchor:=Me.Cells(l, 1), Address:="",_
            SubAddress:="Start" & wSheet.Index, TextToDisplay:=wSheet.Name
     End If
    Next wSheet
End Sub

Press Alt/figs/command.gif-Q to get back to your workbook and then save your changes. Notice that the code names (such as when you name a cell or range of cells in Excel) cell A1 on each sheet Start, plus a unique whole number representing the index number of the sheet . This ensures that A1 on each sheet has a different name. If A1 on your worksheet already has a name, you should consider changing any mention of A1 in the code to something more suitable-an unused cell anywhere on the sheet, for instance.

You should be aware that if you select File » Properties » Summary and enter a URL as a hyperlink base, the index created from the preceding code possibly will not work. A hyperlink base is a path or URL that you want to use for all hyperlinks with the same base address that are inserted in the current document.

Another, more user-friendly, way of constructing an index is to add a link to the list of sheets as a context-menu item, keeping it just a right-click away. We'll have that link open the standard workbook tabs command bar. You generally get to this command bar by right-clicking any of the sheet tab scroll arrows on the bottom left of any worksheet, as shown in figure.

Figure. Tabs command bar displayed by right-clicking the sheet scroll tabs

To link that tab's command bar to a right-click in any cell, enter the following code in the VBE:

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target
As Range, Cancel As Boolean)
Dim cCont As CommandBarButton
    On Error Resume Next
    Application.CommandBars("Cell").Controls("Sheet Index").Delete
    On Error GoTo 0
        Set cCont = Application.CommandBars("Cell").Controls.Add _
                        (Type:=msoControlButton, Temporary:=True)
        With cCont
                  .Caption = "Sheet Index"
             .OnAction = "IndexCode"
            End With
End Sub

Next, you'll need to insert a standard module to house the IndexCode macro, called by the preceding code whenever the user right-clicks in a cell. It is vital that you use a standard module next, as placing the code in the same module as Workbook_SheetBeforeRightClick will mean Excel will not know where to find the macro called IndexCode.

Select Insert » Module and enter the following code:

Sub IndexCode( )
 Application.CommandBars("workbook Tabs").ShowPopup
End Sub

Press Alt/figs/command.gif-Q to get back to the Excel interface.

Now, right-click within any cell on any worksheet and you should see a new menu item called Sheet Index that will take you right to a list of sheets in the workbook.