Categories
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. In this tutorial, you’ll learn to create an index in a couple of ways.

  1. Manually Creating Worksheets Index
  2. Creating Worksheets Index with HYPERLINK Formula
  3. Using VBA to Create Sheet Index
  4. Showing Sheets Index in Right-Mouse Click Context Menu

Manually Creating Sheet Index

You might be tempted to create the index manually:

  1. Create a new worksheet, call it Index or the like,
  2. Enter a list of all your worksheet’s names, and
  3. Hyperlink each to the appropriate sheet by selecting Insert » Link or by pressing Ctrl+K (or ⌘+K).
  4. In the dialog box, click Place in This Document and then select the sheet you want to link.

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.

Using Excel Formula to Create Worksheet Index

You can also use the HYPERLINK formula to create an index of worksheets manually in Excel:

=HYPERLINK("#Sheet1!A1", "Go to Sheet1")
  1. In cell A1 type the name of the first worksheet in your workbook.
  2. Drag the fill handle down to populate the rest of column A with worksheet names.
  3. In cell B1, type this formula:
    =HYPERLINK("#'"&A1&"'!A1","Go to "&A1)
  4. Drag the fill handle down to populate the rest of column B with worksheet links.

Using VBA to Automatically Create Index of All the Sheets

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.

  1. Insert a new worksheet into your workbook and name it Index.
  2. Right-click the index sheet’s tab and select View Code from the context menu. Or click Alt+F11 ( for Mac Opt+F11 or Fn+Opt+F11).
  3. Enter the following Visual Basic code in the Sheet1 (Index) object:
Private Sub Worksheet_Activate()
 Dim wSheet As Worksheet
 Dim l As Long
 l = 1
 With Me
  .Columns(1).ClearContents
  .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+Q (or ⌘+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.

Create Worksheet Index When Right-Click on a Cell

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.

  1. 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("Show Sheets").Delete
 On Error GoTo 0
  Set cCont = Application.CommandBars("Cell").Controls.Add(Type:=msoControlButton, Temporary:=True)
  With cCont
   .Caption = "Show Sheets"
   .OnAction = "sheetsPopup"
  End With
End Sub
  1. Next, you’ll need to insert a standard module to house the sheetsPopup 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 sheetsPopup.

    In VBA window, select Insert » Module and enter the following code:
Sub sheetsPopup( )
 Application.CommandBars("workbook Tabs").ShowPopup
End Sub
  1. Press Alt+Q or ⌘+Q to get back to the Excel interface.
  1. Now, right-click within any cell on any worksheet and you should see a new menu item called Popup Sheets that will take you right to a list of sheets in the workbook.

Understanding Workbooks and Worksheets: