ToC for sheets?

Z

Zilla

Is there a "Table of Contents" function for sheets, so
if I have Sheet1 cells with the names of the other sheets,
I click on the sheet name and it goes to that shteet?
 
D

Don Guillett

Use this sub to make your list

Sub listsheets()
For i = 1 To Sheets.Count
Cells(i, 1) = Sheets(i).Name
Next i
End Sub

Put this sub into the SHEET module of the sheet with the list. Then double
click on the cell with the sheet name

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Application.DisplayAlerts = False
Dim WantedSheet As String
WantedSheet = Trim(ActiveCell.Value)
If WantedSheet = "" Then Exit Sub
On Error Resume Next
If Sheets(ActiveCell.Value) Is Nothing Then
'GetWorkbook ' calls another macro to do that
Else
Application.Goto Sheets(ActiveCell.Value).Range("a4")
End If
Application.DisplayAlerts = True
End Sub
 
H

Harlan Grove

Don Guillett said:
The archives want to know your final solution.
Click to expand...
....

Why? Different approaches may work better for different people.
There's plenty of aggressive, er, peer review in these newsgroups, so
flawed approaches are almost always pointed out.

FTHOI, if the OP uses Excel 2002 or later, there's also XLM. Define
the name WorksheetList referring to

=TRANSPOSE(SUBSTITUTE(GET.WORKBOOK(1),"["&GET.DOCUMENT(88)&"]",""))

then create the TOC with formulas like

B2: =INDEX(WorksheetList,ROWS(B$2:B2))

filled down into B3:B<whatever>.

WARNING: copying cells with formulas that refer to defined names
calling XLM functions and attempting to paste them into cells in other
worksheets will CRASH earlier versions of Excel with COMPLETE DATA
LOSS.
 
Top