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
-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.
This code should live in the private
module for the
Sheet object. Insert a new
worksheet into your workbook and name it something
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
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
-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)
A1 on each sheet
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
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 1-11.
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
IndexCode macro, called by the preceding
code whenever the user right-clicks in a cell. It is vital
you use a standard module next, as placing the code in the same
Workbook_SheetBeforeRightClick will mean
Excel will not know where to find the macro called
Sub IndexCode( ) Application.CommandBars("workbook Tabs").ShowPopup End Sub
-Q to get back to the Excel interface.