Excel VBA insert a hyperlink

Question

I have been trying to get a macro insert a dynamic hyperlink for a couple hours now and cannot seem to get the syntax correct.

I need to make my macro insert a hyperlink into rLinkCell that points to rTargetCell in shTargetSheet, use the label or display text stLinkName and have the Screen tip stTip.

I have gotten this far:

shCurSheet.Hyperlinks.Add Anchor:=rLinkCell, _
           Address:="", _
           SubAddress:=shTargetSheet.Name & "!" & rTargetCell.Address, _
           ScreenTip:=stTip, _
           TextToDisplay:=stLinkName

It makes the link perfectly and all the displays are fine, however when I click it I get an error saying Refrence is not valid.

I am certain it is something simple, what am I doing wrong?

Please post the rest of your code, or at least a minimum example (where you declare and assign your variables) so that we can attempt to resolve the problem. I think you need to be doing this to the Address property, not the SubAddress property. — David Zemens
– David Zemens, Commented May 12, 2014 at 19:05

Bjoern Stiel · Accepted Answer · 2014-05-13 16:45:14Z

1

If all you want to do is create a hyperlink on a sheet, you can just use the =HYPERLINK() function.

I've just create an example workbook, here's the formula solution: https://spreadgit.com/bjoern/hyperlink-example.xlsb/sheets/Sheet1

and here's the VBA solution: https://spreadgit.com/bjoern/hyperlink-example.xlsb/modules/Module1

edited May 13, 2014 at 16:45

answered May 13, 2014 at 16:37

Bjoern Stiel

4,1911 gold badge25 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel VBA insert a hyperlink

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related