VBA to open Excel hyperlink does not work when hyperlink generated with a formula

by

I’m wondering if anyone has had a similar problem, and why the formula
generated hyperlinks are not working for me.

Alas, this seems to be painful truth: Excel does not add to Hyperlinks collection formula-generated links – below is the screen from the debugger which is pointed to =HYPERLINK("http://www.google.com/";"Google"):

Cell Hyperlinks collection

I’m not sure whether this is a deliberate implementation or a bug, but yes, formula-generated links may NOT be opened using Hyperlinks().Follow method.

However, if you’re going to use keyboard shortcut for links opening, just use the following code – it will automatically convert to clickable link selected cell text and open it:

Sub Open_Hyperlink()
    Selection.Hyperlinks.Add Anchor:=Selection, Address:=Selection.Formula
    Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
End Sub

Just assign any shortcut and you’re ready to go) Here is the sample: https://www.dropbox.com/s/d4cie7lun22quma/FollowLinks.xlsm

Hope that’s somehow helpful. Good luck!

Leave a Comment