VBA Hyperlink Question
Table of Contents
Sheet5
Master
Sheet 3
Sheet one
Master (2)
Master (3)
is an example and all the hyperlinks worked.
--
Regards,
Tom Ogilvy
Tom Ogilvy wrote in message
...
Yes, before posted, I did and it worked fine.
Perhaps you are not making the change correctly
Here is the whole routine - copy and paste it into a new module zand test
it
Sub TableOfContents2_AAA()
Dim ws As Worksheet, wsTOC As Worksheet
Dim r As Long
Application.ScreenUpdating = False
Set wsTOC = ActiveWorkbook.Worksheets.Add _
(befo=ActiveWorkbook.Sheets(1))
wsTOC.Name = "Table of Contents"
wsTOC.Range("A1") = "Table of Contents"
wsTOC.Range("A1").Font.Size = 18
r = 3
For Each ws In ActiveWorkbook.Worksheets
If ws.Name < wsTOC.Name Then
wsTOC.Hyperlinks.Add anchor:= _
wsTOC.Cells(r, 1), Address:="", _
SubAddress:="'" & ws.Name & "'!A1", _
TextToDisplay:=ws.Name
'wsTOC.Cells(r, 1).Value = ws.Name
r = r + 1
End If
Next
Application.ScreenUpdating = True
End Sub
I tested it in Excel 97 which did not have the TextToDisplay argument, so
I
used the value of the cell, but I have commented out that line and
restored
the TextToDisplay argument.
--
Regards,
Tom Ogilvy
onliner wrote in message
news:53iQa.942$Ze.503@fed1read03...
Thanks, Tom.
Did you get a chance to test this?
I can't get it to work.
Appreciate your feedback.
"Tom Ogilvy" wrote in message
...
Change
SubAddress:=ws.Name & "!A1
to
SubAddress:="'" & wsName & "'!A1"
Note that I have put -- doublequote singlequote doublequote & <--
before
swName
and in "!A1" I have added a singlequote after the first doublequote.
Regards,
Tom Ogilvy
onliner wrote in message
news:hMfQa.925$Ze.806@fed1read03...
The following code creates a Table of Contents complete with
hyperlinks
to
each of the worksheets within the workbook.
The problem is that the hyperlinks only work for sheets with one
name,
for
example, "sheet1". For sheets with multi-word names like "sheet
one",
the
hyperlinks don't work.
Anyone know how to fix this?
Sub TableOfContents2()
Dim ws As Worksheet, wsTOC As Worksheet
Dim r As Long
Application.ScreenUpdating = False
Set wsTOC = ActiveWorkbook.Worksheets.Add
(befo=ActiveWorkbook.Sheets(1))
wsTOC.Name = "Table of Contents"
wsTOC.Range("A1") = "Table of Contents"
wsTOC.Range("A1").Font.Size = 18
r = 3
For Each ws In ActiveWorkbook.Worksheets
If ws.Name < wsTOC.Name Then
wsTOC.Hyperlinks.Add anchor:=wsTOC.Cells(r, 1), Address:="", _
SubAddress:=ws.Name & "!A1", TextToDisplay:=ws.Name
r = r + 1
End If
Next
Application.ScreenUpdating = True
End Sub
|