Excel: retrieve hyperlink from an image in a cell

Question

In an Excel sheet (created by importing external data and images) are several cells that each contain different image and that image has a hyperlink. These cells containing images are all in one column.

How can I retrieve that hyperlink from each image? The hyperlink is NOT retrievable from that cell.

I've got a user-defined function that easily retrieves hyperlinks from any cell (if there is a hyperlink).

Function GetURL(rng As Range) As String
On Error Resume Next
GetURL = rng.Hyperlinks(1).Address
End Function

This function can be placed anywhere. For example: In cell c25 the URL from a hyperlink in cell A1 must be listed. For that simply enter the following formula in cell c25.

=GetURL(A1)

That works just fine.

However, if that cell A1 contains an image with a hyperlink, then the result is nil (or nothing). Even though the hyperlink is there as part of that image.

Apparently the image is an object that needs to be addressed. How can this be done?

Just found a possible duplicate. Unfortunately the answer didn't get me any further. Please add sample code. Thanks.

SeanC SeanC · Accepted Answer · 2014-12-12T21:02:29

A shape also has a Hyperlink.Address
A shape is defined on a sheet, and also has a name.
Putting these together, I came up with the following function:

Function PicURL(Pic As String, Optional SheetName As String) As String
If SheetName = "" Then SheetName = Application.Caller.Worksheet.Name
On Error Resume Next
PicURL = Sheets(SheetName).Shapes(Pic).Hyperlink.Address
End Function

Use: If picture is on the same sheet, then =PicURL("Picture 1") will return the target address.
If the picture is on another sheet, then =PicURL("Picture 1","Sheet1") would be needed.
If the picture is in a different worksheet, then that's a whole different ballgame, and I leave that as an exercise for the reader.

Excel: retrieve hyperlink from an image in a cell

1 Answers