VBA: Creating hyperlink in cell A2 of all visible worksheets except Cover Page and Table of Contents

Josheru

New Member
Joined
Feb 21, 2019
Messages
3
Hey guys/gals!

So as the title indicates, I don't know how to do this. I know how to make hyperlinks with VBA, but not on all visible pages.

Additionally, I was hoping to make every hyperlink retain whatever text is already in cell A2.

Here's why I'm doing it: I want to have people be able to navigate from my TOC's hyperlinks (which I've already created) to each worksheet, but also navigate back to the TOC via hyperlink. The reason why I want to keep the cell text is because I want to hide the link behind the title for the page (cell A2), as I don't want to rely on my team remembering to hide the link when it's time to put our client presentation into PDF.

If anyone can help me, I would greatly appreciate it.

Thanks!
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Code:
Public Sub LinksBackToTOC()
    Dim WS As Worksheet
    
    For Each WS In ThisWorkbook.Worksheets
    If WS.Name <> "TOC" And WS.Name <> "Cover Page" Then
    With WS.Range("A2")
    
        WS.Hyperlinks.Add .Offset, vbNullString, "TOC!A1", "Back to Table of Contents", CStr(.Value)
    
    End With
    End If
    Next
End Sub
 
Upvote 0
Code:
Public Sub LinksBackToTOC()
    Dim WS As Worksheet
    
    For Each WS In ThisWorkbook.Worksheets
    If WS.Name <> "TOC" And WS.Name <> "Cover Page" Then
    With WS.Range("A2")
    
        WS.Hyperlinks.Add .Offset, vbNullString, "TOC!A1", "Back to Table of Contents", CStr(.Value)
    
    End With
    End If
    Next
End Sub

Thank you! It looks like everything works except the hyperlink, which is saying there's an invalid reference? I figured it was because I have a different name for my TOC (I used "Table of Contents"), but I'm still receiving the error. Any thoughts?

Here's how I wrote it:

Public Sub LinksBackToTOC()
Dim WS As Worksheet

For Each WS In ThisWorkbook.Worksheets
If WS.Name <> "Table of Contents" And WS.Name <> "Cover Page" Then
With WS.Range("A2")

WS.Hyperlinks.Add .Offset, vbNullString, "Table of Contents!A1", "Back to Table of Contents", CStr(.Value)

End With
End If
Next
End Sub
 
Upvote 0
You need to add apostrophes before & after the sheet name as it has spaces in it, like
Code:
        WS.Hyperlinks.Add .Offset, vbNullString, "'table of contents'!A1", "Back to Table of Contents", CStr(.Value)
 
Upvote 0
When you use sheet names with spaces you must use the quote

Try this:

Code:
WS.Hyperlinks.Add .Offset, vbNullString, "[SIZE=3][B][COLOR=#0000ff]'[/COLOR][/B][/SIZE]Table of Contents[SIZE=3][B][COLOR=#0000ff]'[/COLOR][/B][/SIZE]!A1", "Back to Table of Contents", CStr(.Value)
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0
Since the tab name has spaces, you have to enclose it in single quotes:
Code:
'Table of Contents'!A1
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top