Macro to follow dynamic hyperlinks

[samcdavies]

I want to use a piece of VBA or a macro to follow a dynamic hyperlink and then carry out an action.

The dynamic hyperlink would look like this:
=hyperlink('#sheetname!B'&B2,"Link")

And the VBA or macro I'm trying is a simple;
Range("B2").Hyperlinks(1).Follow (True)

This doesn't work - it brings up a debug window. It will only work when the hyperlink is absolute (ie, I've right clicked the cell and setup the hyperlink that way) - this isn't sufficient for my requirements - is there anything I can do to make this VBA/macro work with a dynamic hyperlink formula?

Thanks in advance,
Sam

[Norie]

The HYPERLINK worksheet functio doesn't create a hyperlink attached to the cell as you would when you right click and goto Hyperlink...

So there are no items in the Hyperlinks collection for the cell.

Have you considered using code to add the hyperlink(s) instead of fomula(s)?

[samcdavies]

Thanks for the idea Norie. I also did it this way for anybody interested;

A1 contains sheet name Sheet1! and B1 contains cell reference (in this case G2).

The macro is;

Sub Macro4()
'
' Macro4 Macro
' Macro recorded 28/11/2013 by daviescs
'

'
Range("D3").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
Range("A1") & Range("B1")
Range("d3").Hyperlinks(1).Follow (True)


End Sub

[Norie]

Here's a simple example.

Please Login or Register  to view this content.