VBA Hyperlink Question
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
|