Excel Hacks Ebook [Electronic resources]

Hack 7 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/-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 appropriateIndex, 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 suitablean unused cell anywhere on the sheet, for instance.

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.

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/-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.