Worksheet name list

[Sundog]

In Excel 2007, I have a workbook that has many worksheets. On the first sheet, I want to place a list of worksheet names that are hyperlinked to A1 on the selected worksheet. Sorta like a Table of Contents. Is there a function or VBA that I can use to list the worksheet names?

[HansV]

Right-click the sheet tab of the first sheet, and select View Code from the popup menu.

Enter or copy the following code into the worksheet module that appears:

Code: Select all

Private Sub Worksheet_Activate()
  Dim wsh As Worksheet
  Dim r As Long
  r = 0
  Columns(1).ClearContents
  For Each wsh In Worksheets
    If wsh.Name <> Me.Name Then
      r = r + 1
      Me.Hyperlinks.Add Anchor:=Range("A" & r), Address:="", _
        SubAddress:="'" & wsh.Name & "'!A1", TextToDisplay:=wsh.Name
    End If
  Next wsh
End Sub

The list will automatically be updated each time you switch to the first sheet.

[HansV]

BTW, building an index of sheets is one of the tools in the popular ASAP Utilities (free for personal use).

[Sundog]

Perfect. Thanks, Hans. I'm using it as the first sheet in my copy of the wonderful Word ribbon mapping workbook.

(This is the one that maps Word 2003 commands to the corresponding Word 2007 ribbon thingee, with 52 tabs--including one titled Japanese Greetings toolbar.)

And thanks for the reminder about ASAP Utilities; had them for Excel 2003, will get them for 2007.