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.

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.

Figure 1-11. 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/-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.