Worksheet name list
-
- 5StarLounger
- Posts: 704
- Joined: 28 Jan 2010, 22:47
- Location: Alien Country (Roswell NM)
Worksheet name list
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?
Sundog
-
- Administrator
- Posts: 78790
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Worksheet name list
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:
The list will automatically be updated each time you switch to the first sheet.
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
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78790
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Worksheet name list
BTW, building an index of sheets is one of the tools in the popular ASAP Utilities (free for personal use).
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 704
- Joined: 28 Jan 2010, 22:47
- Location: Alien Country (Roswell NM)
Re: Worksheet name list
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.
(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.
Sundog