Solved create hyperlinked index to excel tabs

Microsoft Office excel 2007 - upgrade
December 15, 2010 at 09:07:41
Specs: Windows 7
I have excel workbook containing multiple worksheets. I want to insert a new worksheet in the workbook to contain index of all the worksheets contained in the workbook. I dont have a list of all the worksheets in the work book, therefore need a way to create Hyperlink and list of worksheets from the tabs in this document...is this possible? thanks.

See More: create hyperlinked index to excel tabs

Report •


✔ Best Answer
December 15, 2010 at 15:21:21
The following code (which I didn't write) will do what you've asked.

I offered the other suggestion just it case it fit your needs.

In any case, paste this code into the VBA editor and run it.

I suggest you try it in a backup copy of your workbook since workbooks can not be undone.

Again, I didn't write it, but I've used it and it works as advertised. If you add new sheets or change names, just run it again and it will delete the old Table of Contents (after asking) and create a new one.

Option Explicit
 
Sub CreateTOC()
     'Declare all variables
    Dim ws As Worksheet, curws As Worksheet, shtName As String
    Dim nRow As Long, i As Long, N As Long, x As Long, tmpCount As Long
    Dim cLeft, cTop, cHeight, cWidth, cb As Shape, strMsg As String
    Dim cCnt As Long, cAddy As String, cShade As Long
     'Check if a workbook is open or not.  If no workbook is open, quit.
    If ActiveWorkbook Is Nothing Then
        MsgBox "You must have a workbook open first!", vbInformation, _
                "No Open Book"
        Exit Sub
    End If
     '--------------------------------------------------------
    cShade = 37 '<<== SET BACKGROUND COLOR DESIRED HERE
     '--------------------------------------------------------
     'Turn off events and screen flickering.
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    nRow = 4: x = 0
     'Check if sheet exists already; direct where to go if not.
    On Error GoTo hasSheet
    Sheets("TOC").Activate
     'Confirm the desire to overwrite sheet if it exists already.
    If MsgBox("You already have a Table of Contents page." _
    & vbLf & vbLf & _
    "Would you like to overwrite it?", _
    vbYesNo + vbQuestion, "Replace TOC page?") = vbYes Then GoTo createNew
    Exit Sub
hasSheet:
    x = 1
     'Add sheet as the first sheet in the workbook.
    Sheets.Add before:=Sheets(1)
    GoTo hasNew
createNew:
    Sheets("TOC").Delete
    GoTo hasSheet
hasNew:
     'Reset error statment/redirects
    On Error GoTo 0
     'Set chart sheet varible counter
    tmpCount = ActiveWorkbook.Charts.Count
    If tmpCount > 0 Then tmpCount = 1
     'Set a little formatting for the TOC sheet.
    ActiveSheet.Name = "TOC"
    With Sheets("TOC")
        .Cells.Interior.ColorIndex = cShade
        .Rows("4:65536").RowHeight = 16
        .Range("A1").Value = "Designed by VBAX"
        .Range("A1").Font.Bold = False
        .Range("A1").Font.Italic = True
        .Range("A1").Font.Name = "Arial"
        .Range("A1").Font.Size = "8"
        .Range("A2").Value = "Table of Contents"
        .Range("A2").Font.Bold = True
        .Range("A2").Font.Name = "Arial"
        .Range("A2").Font.Size = "24"
        .Range("A4").Select
    End With
     'Set variables for loop/iterations
    N = ActiveWorkbook.Sheets.Count + tmpCount
    If x = 1 Then N = N - 1
    For i = 2 To N
        With Sheets("TOC")
                shtName = Sheets(i).Name
                 'Add a hyperlink to A1 of each sheet.
                .Range("C" & nRow).Hyperlinks.Add _
                Anchor:=.Range("C" & nRow), Address:="#'" & _
                shtName & "'!A1", TextToDisplay:=shtName
                .Range("C" & nRow).HorizontalAlignment = xlLeft
            .Range("B" & nRow).Value = nRow - 2
            nRow = nRow + 1
        End With
continueLoop:
    Next i
     'Perform some last minute formatting.
    With Sheets("TOC")
        .Range("C:C").EntireColumn.AutoFit
        .Range("A4").Activate
    End With
     'Turn events back on.
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    strMsg = vbNewLine & vbNewLine & "Please note: " & _
    "Charts will have hyperlinks associated with an object."
     'Toggle message box for chart existence or not, information only.
    If cCnt = 0 Then strMsg = ""
    MsgBox "Complete!" & strMsg, vbInformation, "Complete!"
End Sub

Posting Tip: Before posting Data or VBA Code, read this How-To.



#1
December 15, 2010 at 10:17:11
Yes it's possible, but before I offer a way to do that I'd like to know if you are you familiar with this technique:

You know those little arrows to the left of the first sheet tab?

Right Click anywhere over those arrows.

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#2
December 15, 2010 at 11:03:01
hi DerbyDad, thanks for your reply. Unfortunately, Iam not familiar at all with this method. I have the vba screen open now, not sure where to go after that...

Report •

#3
December 15, 2010 at 11:50:48
I was talking about the arrows next to the sheet tabs in the workbook not the VBA editor.. The arrows that you use to move the Sheet tabs left and right. The arrows below the row numbers.

Right Click in that area and see what happens.

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

Related Solutions

#4
December 15, 2010 at 14:08:51
Hi, thanks again for the reply. If I understand you correctly, you are refering to the drop down list that appears and showing in order of appearance, all the tabs contained in the workbook.
What I am actually looking to do is insert a new worksheet at the beging so it is the first tab in the workbook and call this sheet "Index". I would like to somehow populate in this new worksheet a list of all the tabs contained in the workbook and have a hyperlink to each. Is this possible?

Report •

#5
December 15, 2010 at 15:21:21
✔ Best Answer
The following code (which I didn't write) will do what you've asked.

I offered the other suggestion just it case it fit your needs.

In any case, paste this code into the VBA editor and run it.

I suggest you try it in a backup copy of your workbook since workbooks can not be undone.

Again, I didn't write it, but I've used it and it works as advertised. If you add new sheets or change names, just run it again and it will delete the old Table of Contents (after asking) and create a new one.

Option Explicit
 
Sub CreateTOC()
     'Declare all variables
    Dim ws As Worksheet, curws As Worksheet, shtName As String
    Dim nRow As Long, i As Long, N As Long, x As Long, tmpCount As Long
    Dim cLeft, cTop, cHeight, cWidth, cb As Shape, strMsg As String
    Dim cCnt As Long, cAddy As String, cShade As Long
     'Check if a workbook is open or not.  If no workbook is open, quit.
    If ActiveWorkbook Is Nothing Then
        MsgBox "You must have a workbook open first!", vbInformation, _
                "No Open Book"
        Exit Sub
    End If
     '--------------------------------------------------------
    cShade = 37 '<<== SET BACKGROUND COLOR DESIRED HERE
     '--------------------------------------------------------
     'Turn off events and screen flickering.
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    nRow = 4: x = 0
     'Check if sheet exists already; direct where to go if not.
    On Error GoTo hasSheet
    Sheets("TOC").Activate
     'Confirm the desire to overwrite sheet if it exists already.
    If MsgBox("You already have a Table of Contents page." _
    & vbLf & vbLf & _
    "Would you like to overwrite it?", _
    vbYesNo + vbQuestion, "Replace TOC page?") = vbYes Then GoTo createNew
    Exit Sub
hasSheet:
    x = 1
     'Add sheet as the first sheet in the workbook.
    Sheets.Add before:=Sheets(1)
    GoTo hasNew
createNew:
    Sheets("TOC").Delete
    GoTo hasSheet
hasNew:
     'Reset error statment/redirects
    On Error GoTo 0
     'Set chart sheet varible counter
    tmpCount = ActiveWorkbook.Charts.Count
    If tmpCount > 0 Then tmpCount = 1
     'Set a little formatting for the TOC sheet.
    ActiveSheet.Name = "TOC"
    With Sheets("TOC")
        .Cells.Interior.ColorIndex = cShade
        .Rows("4:65536").RowHeight = 16
        .Range("A1").Value = "Designed by VBAX"
        .Range("A1").Font.Bold = False
        .Range("A1").Font.Italic = True
        .Range("A1").Font.Name = "Arial"
        .Range("A1").Font.Size = "8"
        .Range("A2").Value = "Table of Contents"
        .Range("A2").Font.Bold = True
        .Range("A2").Font.Name = "Arial"
        .Range("A2").Font.Size = "24"
        .Range("A4").Select
    End With
     'Set variables for loop/iterations
    N = ActiveWorkbook.Sheets.Count + tmpCount
    If x = 1 Then N = N - 1
    For i = 2 To N
        With Sheets("TOC")
                shtName = Sheets(i).Name
                 'Add a hyperlink to A1 of each sheet.
                .Range("C" & nRow).Hyperlinks.Add _
                Anchor:=.Range("C" & nRow), Address:="#'" & _
                shtName & "'!A1", TextToDisplay:=shtName
                .Range("C" & nRow).HorizontalAlignment = xlLeft
            .Range("B" & nRow).Value = nRow - 2
            nRow = nRow + 1
        End With
continueLoop:
    Next i
     'Perform some last minute formatting.
    With Sheets("TOC")
        .Range("C:C").EntireColumn.AutoFit
        .Range("A4").Activate
    End With
     'Turn events back on.
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    strMsg = vbNewLine & vbNewLine & "Please note: " & _
    "Charts will have hyperlinks associated with an object."
     'Toggle message box for chart existence or not, information only.
    If cCnt = 0 Then strMsg = ""
    MsgBox "Complete!" & strMsg, vbInformation, "Complete!"
End Sub

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#6
December 16, 2010 at 03:06:43
That worked excellent, thanks a mil!!

Report •

#7
December 16, 2010 at 06:39:12
Glad I could help.

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#8
December 27, 2010 at 03:59:29
hey, i used ur VBA editor to create index but actually i also wanted the page numbers next to the sheets name. If you could help it would be really great

Report •

#9
December 27, 2010 at 15:31:53
I'm not sure what you are asking for.

When I run the code I get "page numbers":

2 Sheet1
3 Sheet2

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#10
January 19, 2011 at 14:30:15
This macro for creating a hyperlinked index or Table of Contents is way cool. This saved me a ton of time. Thanks a million.

Report •


Ask Question