Table of Contents

[Laurel Jordan]

How do I add a table of contents to an Excel Workbook? I have a workbook that contains many worksheets (50+) and I want an easy way to access each tab without having to scroll.

[ This Message was edited by: Laurel Jordan on 2002-05-08 12:45 ]

[Brian from Maui]

Laurel,

Try hyperlinking

[Dave Patton]

I use an index page zIndex.

Sub ListSheets()
' List includes a hyperlink for each sheet
' - sorts the list
' - includes description from H1 of each Sheet
' - includes File Name in Column C
' - currently doesn't allow spaces in sheetnames
' This is OK since I do not put spaces in sheet names since
' such spaces create problems with a variety of formulas.
Dim i As Integer
Dim cRow As Long
Application.ScreenUpdating = False
cRow = 2
For i = 1 To ActiveWorkbook.Sheets.Count
Cells(cRow - 1 + i, 3) = "'" & Application.ActiveWorkbook.Name
Cells(cRow - 1 + i, 4) = "'" & Sheets(i).Name
'Cells(cRow - 1 + i, 2).Value = "=indirect(address(1,8,,,rSheetNames))" ' see next line
Cells(cRow - 1 + i, 2).Value = "=INDIRECT(RC[2]&""!""&CELL(""address"",R1C8))"
ActiveSheet.Hyperlinks.Add Anchor:=Cells(cRow - 1 + i, 4), Address:="", SubAddress:=Sheets(i).Name & "!A1"
Next
Cells(cRow - 1 + i, 4).Select
Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("B1") = "Description"
Range("C1") = "File Name"
Range("D1") = "Sheet Names"
Rows("1:1").Font.Bold = True
With Range("A1").CurrentRegion
.Columns.AutoFit
.Font.Size = 12
End With
Application.ScreenUpdating = True
End Sub

[ This Message was edited by: Dave Patton on 2002-05-08 11:23 ]

[Nimrod]

Opt1- Create custom views and insert custom view drop down box on menu bar. Then just select desired sheet from drop down

Opt2- Instert "name box" onto tool bar and type in the Sheetname and a cell ie. Sheet50!A1

Opt3- Create a series of links from on reference page ... for details look up "create shortcuts to worksheets" in excel help

Opt4- Create a reference sheet with fifty command buttons with one line macro saying what page to select

[CompTutor]

If you right-click on the tab section of the horizontal scroll bar (left end), it shows a ready-made "TOC"; clicking on the one you want should take you there...

[Yogi Anand]

On 2002-05-08 11:33, CompTutor wrote:
If you right-click on the tab section of the horizontal scroll bar (left end), it shows a ready-made "TOC"; clicking on the one you want should take you there...

Hi CompTutor:
This will work only if the SheetTabs option is activated in TOOLS|VIEW|WINDOW_OPTIONS|
In some cases the developer of the workbook may choose to not show the sheets.

Regards!

[CompTutor]

Yogi Anand, thanks for that clarification. (I started hanging here because I discovered that I do NOT know how things are used in the "real" world, and I appreciate your response.)

[Mark W.]

On 2002-05-08 11:45, Yogi Anand wrote:

Hi CompTutor:
This will work only if the SheetTabs option is activated in TOOLS|VIEW|WINDOW_OPTIONS|
In some cases the developer of the workbook may choose to not show the sheets.

Regards!

How do I add a table of contents to an Excel Workbook? I have a workbook that contains many worksheets (50+) and I want an easy way to access each tab without having to scroll.

However, if they didn't have worksheet tabs they wouldn't have the tab scrolling buttons either.

[Yogi Anand]

On 2002-05-08 15:48, CompTutor wrote:
Yogi Anand, thanks for that clarification. (I started hanging here because I discovered that I do NOT know how things are used in the "real" world, and I appreciate your response.)

Hi CompTutor:
Your contribution was a great one -- I just tagged on to it to enhance its value and not to diminish from it. I am glad you took my comment in the positive light.
And, I believe what Mark W is saying -- hey, if the Sheet Tabs are there, let us make use of them as you proposed.

Regards!