Hyperlink issue vba

[Tmatti92]

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.

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
	A	B
1	Table Of Contents
2	Page 1	Page 1
3	Page 2	Page 2
4	Page 3	Page 3
5	Page 4	Page 4
TOC

If you could help it would be much appreciated.

Thanks,
Tmatti92

 

[Fluff]

Hi & welcome to MrExcel.
At what point is it saying the reference is invalid?

 

[Tmatti92]

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.

 

[Fluff]

The code you posted works fine for me. Do none of the links work, or just the one for Page4?

 

[Tmatti92]

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]

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]

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]

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.

ActiveSheet.Hyperlinks.Add Anchor:=startcell, Address:="", SubAddress:= _
        "'"shname & "'!A1", TextToDisplay:=shname