Hyperlink issue vba

Tmatti92

New Member
Joined
Feb 19, 2021
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
Hi everyone,

I am quite new to using VBA and I am having issues using hyperlinks. Essentially I am trying to create a table of contents where if I select the hyperlink (In the same document) on the table of contents sheet it will simply direct me to the following sheet i.e. selecting page 1 will direct me to page 1. However, it is showing that the reference is not valid.

VBA Code:
Sub auto_TOC()
 
Dim sh As Worksheet
Dim startcell As Range
Dim shname As String

Set startcell = Excel.Application.InputBox("Select where you want to insert" _
& "table of contents", "Table Of Contents", , , , , , 8)

Set startcell = startcell.Cells(1, 1)


For Each sh In Worksheets
shname = sh.Name
    If ActiveSheet.Name <> sh.Name Then
        ActiveSheet.Hyperlinks.Add Anchor:=startcell, Address:="", SubAddress:= _
        shname & "!A1", TextToDisplay:=shname
        startcell.Offset(0, 1).Value = sh.Range("A1").Value
        Set startcell = startcell.Offset(1, 0)
    End If
Next sh
End Sub

Book1
AB
1Table Of Contents
2Page 1Page 1
3Page 2Page 2
4Page 3Page 3
5Page 4Page 4
TOC


If you could help it would be much appreciated.

Thanks,
Tmatti92
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Hi & welcome to MrExcel.
At what point is it saying the reference is invalid?
 
Upvote 0
Hi & welcome to MrExcel.
At what point is it saying the reference is invalid?
Cheers for the reply. So the code seems to be working fine I believe it's just a reference issue. When I select page 1 shown in the minibox above it will display "reference is invalid" I got to this point by using the macro recorder and changed page 1 in the subaddress to the variable shname. However, now it's not directing me to the sheet.
 

Attachments

  • Screenshot 2021-02-19 at 14.43.47.png
    Screenshot 2021-02-19 at 14.43.47.png
    221.3 KB · Views: 14
Upvote 0
The code you posted works fine for me. Do none of the links work, or just the one for Page4?
 
Upvote 0
The code you posted works fine for me. Do none of the links work, or just the one for Page4?
None of them work. I am using a mac, I don't know if that would make a difference.
 
Upvote 0
That may make a difference, as I know nothing about Macs I'm afraid I cannot help any further.
Hopefully somebody else will be able to step in & help.
 
Upvote 0
None of them work. I am using a mac, I don't know if that would make a difference.
Solved! I didn't put quotation marks before the variable, basically not considering the space between Page 1. So amended the sub address as shown below.
VBA Code:
ActiveSheet.Hyperlinks.Add Anchor:=startcell, Address:="", SubAddress:= _
        "'"shname & "'!A1", TextToDisplay:=shname
 
Upvote 0
Solution

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

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