ToC for sheets?
 

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?

--
- Zilla
(Remove XSPAM)

ToC for sheets?
 

Zilla

Try here (bottom of page)

http://www.nickhodge.co.uk/vba/codeexamples.htm

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Zilla" wrote in message
...
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?

--
- Zilla
(Remove XSPAM)

ToC for sheets?
 

Take a look "Hyperlink" in Excel Help menu


"Zilla" wrote:

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?

--
- Zilla
(Remove XSPAM)

ToC for sheets?
 

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

--
Don Guillett
SalesAid Software

"Zilla" wrote in message
...
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?

--
- Zilla
(Remove XSPAM)

ToC for sheets?
 

Thanks to all who replied!

"Zilla" wrote in message
...
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?

--
- Zilla
(Remove XSPAM)

ToC for sheets?
 

The archives want to know your final solution.

--
Don Guillett
SalesAid Software

"Zilla" wrote in message
...
Thanks to all who replied!

"Zilla" wrote in message
...
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?

--
- Zilla
(Remove XSPAM)

ToC for sheets?
 

"Don Guillett" wrote...
The archives want to know your final solution.
....

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.