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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,252
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
At what point is it saying the reference is invalid?
 

Tmatti92

New Member
Joined
Feb 19, 2021
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
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: 5

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,252
Office Version
  1. 365
Platform
  1. Windows
The code you posted works fine for me. Do none of the links work, or just the one for Page4?
 

Tmatti92

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

ADVERTISEMENT

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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,252
Office Version
  1. 365
Platform
  1. Windows
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.
 

Tmatti92

New Member
Joined
Feb 19, 2021
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
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.
Thanks again!
 

Tmatti92

New Member
Joined
Feb 19, 2021
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
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
 
Solution

Forum statistics

Threads
1,141,073
Messages
5,704,141
Members
421,328
Latest member
mippy

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
Top