CODEWITHSUNDEEP
vbaexcelhyperlink
CapnShanty
CapnShanty

Reputation: 559

Can't set worksheet name as subaddress in excel VBA?

Here's my code:

    Dim wsVar As Worksheet
For Each wsVar In ThisWorkbook.Sheets
   With wsVar
 .Hyperlinks.Add Anchor:=.Range("G1"), _
 Address:="", _
 SubAddress:=Sheets("Table of Contents").Name & "!A1", _
 TextToDisplay:="Table of Contents"
   End With
Next wsVar

There's other code above it but that's all irrelevant to my issue. See the subaddress line? I have tried essentially everything to get that subaddress to refer to a worksheet named Table of Contents in this same workbook. It won't work (I also don't know why I need the "!A1", but it seems I do from where else I've looked so it's there). When it does work, it creates a hyperlink, sure, but if you click that hyperlink it says the reference isn't valid. So I know it's this line of the code.

Upvotes: 1

Views: 882

Answers (1)

YowE3K
YowE3K

Reputation: 23974

When a sheet name includes spaces (and a couple of other special characters) you need to enclose it in single quotation marks, i.e. SubAddress:="'Table of Contents'!A1".

(Or SubAddress:="'" & Sheets("Table of Contents").Name & "'!A1", but it's a bit redundant to use the .Name property of a Sheet object to obtain the name you used as the index of the Sheets collection.)

Upvotes: 4

Related Questions