CODEWITHSUNDEEP
excelvbahyperlink
Mark Main
Mark Main

Reputation: 151

Excel VBA Originating Hyperlink Cell Address

Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)

Is it possible to use the Sh object to identify the address of the original cell that invoked the hyperlink? I need to know what cell the hyperlink came from to help with some navigation buttons to move backward (and forward) through previously clicked hyperlinks.

Sh.Name shows the originating Worksheet.Name, but I don't know how to get at the originating address.

Thanks for the help in advance.

Upvotes: 0

Views: 428

Answers (1)

BigBen
BigBen

Reputation: 49998

Since Target is a Hyperlink, Target.Range.Address will return the originating address.

From the Hyperlink.Range documentation:

Returns a Range object that represents the range the specified hyperlink is attached to.

Here is the code tested and the result:

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
    MsgBox "'" & Sh.Name & "'!" & Target.Range.Address
End Sub

enter image description here

Upvotes: 2

Related Questions