Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Table of Contents

  1. #1
    New Member
    Join Date
    May 2002
    Location
    Smucker's
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  2. #2
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Laurel,

    Try hyperlinking

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,499
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default

    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 ]

  4. #4
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  5. #5
    New Member
    Join Date
    May 2002
    Location
    New Jersey
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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...

  6. #6
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  7. #7
    New Member
    Join Date
    May 2002
    Location
    New Jersey
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.)

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-08 11:45, Yogi Anand wrote:
    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!
    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.

  9. #9
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •