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/-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 .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 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
Select Insert » Module and enter the following code:
Sub IndexCode( ) Application.CommandBars("workbook Tabs").ShowPopup End Sub
Press Alt/-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.