VBA code to add hyperlink to Picture

[iantowell]

Hello,
I am using MS Excel 2007 and have inserted by vba code a picture to a cell location (for this forum I will use cell location J8).

I am trying now to add a hyperlink to this picture by using vba code also.

All attempts I have tried so far, puts the link in the cell, not on the picture!

I am wanting to hyperlink to the same workbook but a different sheet.

Can anyone assist?

Thanks

Ian

 

[Tom Urtis]

Although you said you already had code to add a picture to cell J8, here is the code to do that, which segues into the code you asked for regarding how to assign a hyperlink to that picture for the same workbook but to a different worksheet. In this case the picture, when clicked on, will hyperlink to Sheet2 cell A1 so modify that subaddress as needed.

Sub InsertPictureHyperlink()
Dim pct As Picture, iLeft#, iTop#
Dim sFile As String
sFile = "C:\Your\File\Path\YourPicturename.jpg"
If Dir(sFile) = "" Then
MsgBox "Picture file was not found in path!", , "No such animal"
Exit Sub
End If
With Range("J8")
.Select
iLeft = .Left: iTop = .Top
End With
Set pct = ActiveSheet.Pictures.Insert(sFile)
pct.Left = iLeft
pct.Top = iTop
With ActiveSheet
.Hyperlinks.Add Anchor:=.Shapes(pct.Name), Address:="", SubAddress:="Sheet2!A1"
End With
End Sub

 

[iantowell]

Tom,
This is great thank you!

I removed my code and inserted yours, modified it to reflect my cells and worked straight away.

Thanks

Ian

 

[Tom Urtis]

Thanks for the follow-up Ian, much appreciated.

 

[stefankemp]

Hi Tom,

It's fairly easy to add the hyperlink to a picture, but I'm having a really hard time to reference the hyperlink in VBA afterwards. For example I want to see the Screentip property of the hyperlink. I've tried pct.hyperlink.screentip, and shapes(pct.name).hyperlink.screentip, but neither works. Any ideas?

 

[Tom Urtis]

In what way do you want to reference the screen tip? What are you trying to accomplish exactly?

 

[stefankemp]

I have an image on the sheet with a macro assigned to it. I want a screen tip to appear when the mouse pointer hovers over the image. To do this I manually add a hyperlink with a screen tip to the image. But I don't want the hyperlink to be followed when the image is clicked, so I need to set the address property of the hyperlink to nothing (""). But I can't seem to access any of the properties of the image's hyperlink.

 

[Jasa P]

I'm really sorry for disturbing, but please help me! I really need your help..

How to resolve this problem? If I have a combination of 125x10 in cell A1, then I just need the number after the "x". So, the value in cell B1 is 10.


Please help me, I really need to resolve this problem.
Thank you so much.

Regards

 

[CharlesChuckieCharles]

=IFERROR(MID(A1,FIND("x",A1,1)+1,100),"")
put in B1

 

[richardsky]

Dear Mr Urtis,

The code you have written works well for specific picture and hyperlinks. Would you be able to adapt the code to do more than one picture/hyperlink combination i.e. take the image filepaths and hyperlinks from a range of cells? I need that functionality for a job i am doing but the ability to modify your code is out of my league.

With respect in anticipation,
Richard Barsby

Although you said you already had code to add a picture to cell J8, here is the code to do that, which segues into the code you asked for regarding how to assign a hyperlink to that picture for the same workbook but to a different worksheet. In this case the picture, when clicked on, will hyperlink to Sheet2 cell A1 so modify that subaddress as needed.

Sub InsertPictureHyperlink()
Dim pct As Picture, iLeft#, iTop#
Dim sFile As String
sFile = "C:\Your\File\Path\YourPicturename.jpg"
If Dir(sFile) = "" Then
MsgBox "Picture file was not found in path!", , "No such animal"
Exit Sub
End If
With Range("J8")
.Select
iLeft = .Left: iTop = .Top
End With
Set pct = ActiveSheet.Pictures.Insert(sFile)
pct.Left = iLeft
pct.Top = iTop
With ActiveSheet
.Hyperlinks.Add Anchor:=.Shapes(pct.Name), Address:="", SubAddress:="Sheet2!A1"
End With
End Sub