[VBA] hyperlink to another sheet with dynamic name

[Trikson]

Hi there,

Here I come with another question that I can't seem to find answer to anywhere.

I have following code that is responsible for creating hyperlink to a sheet whose name is the same as cell (r, 4) value:

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=Cells(r, 4).Value & "!A1", ScreenTip:=Cells(r, 4).Value, TextToDisplay:="....."

If the sheet name is one word (i.e. 123 or INC or WHATEVER) the code works fine and the hyperlink created works fine.

However if Sheet name is two or more words (i.e. 123 456 or WORLD INC or WHATEVER YOU WISH) the hyperlink is created but does not work (Reference not valid).

I assume it's creating link to sheet named as only one part of the cell.value.

Here's the thing - in VBA excel is using ' to mark the start and end of long names (i.e. '123 456' or 'WORLD INC' or 'WHATEVER YOU WISH') but in my scenario I cannot use explicit names (or Sheet numbers like Sheet1 etc.) because new sheets will be created and code should work for all of them.

So is there any way to achieve what I am aiming for here? Or should I just use "_" in names instead of spaces and forget about any fancy VBA resolution?

 

[Eric Golf]

Hi,

Here's the thing - in VBA excel is using ' to mark the start and end of long names

Single quotes are actually required because there is a space in the sheet name so either your solution of using underscore "_" or you need to add the single quotes in.

e.g.

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="[COLOR="#FF0000"]'[/COLOR]" & Cells(r, 4).Value & "[COLOR="#FF0000"]'[/COLOR]!A1", ScreenTip:=Cells(r, 4).Value, TextToDisplay:="....."

Untested, but hope this helps,

Eric

 

[Trikson]

Hi Eric,

Thanks for your answer.

Your code required a little tinkering but the idea was correct. Adjusted subaddress code to

SubAddress:="'" & Cells(r, 4).Value & "'" & "!A1"

and now it works like a charm.

Thank you once again!

 

[Eric Golf]

Hi,

"[COLOR="#FF0000"]'[/COLOR]!A1"

should give the same result as

"'" & "!A1"

however, as long as it works for you.

Eric

 

[DCEXC]

Hi Eric,

Thanks for your answer.

Your code required a little tinkering but the idea was correct. Adjusted subaddress code to

SubAddress:="'" & Cells(r, 4).Value & "'" & "!A1"

and now it works like a charm.

Thank you once again!

you guys are awesome - 6 years later and this exact thing has been killing me, perfect solution!