Hyperlink Object and the Hyperlinks Collection Example

This example creates a hyperlink-based Table of Contents worksheet:

Sub CreateHyperlinkTOC()

Dim oBk As Workbook

Dim oShtTOC As Worksheet, oSht As

Worksheet

Dim iRow As Integer

Set oBk = ActiveWorkbook

'Add a new sheet to the workbook

Set oShtTOC = oBk.Worksheets.Add

With oShtTOC

'Add the title to the sheet

.Range("A1").Value = "Table of

Contents"

'Add Mail and web hyperlinks

.Hyperlinks.Add .Range("A3"),

"mailto:[email protected]", _

TextToDisplay:="

Email your comments"

.Hyperlinks.Add .Range("A4"),

"http://www.wrox.com", _

TextToDisplay:="

Visit Wrox Press"

End With

'Loop through the sheets in the workbook

'adding location hyperlinks

iRow = 6

For Each oSht In oBk.Worksheets

If oSht.Name <> oShtTOC.Name Then

oShtTOC.Hyperlinks.Add oShtTOC.Cells(iRow, 1), "", _

SubAddress:="'" &

oSht.Name & "'!A1", _

TextToDisplay:=oSht.Name

iRow = iRow + 1

End If

Next

End Sub

0 0

Post a comment