Automatically Open Hyperlink into Excel Worksheet

Question

I"ve put a hyperlink in a worksheet to a 'gif' file.  When I open the spreadsheet, I would like for the image to automatically open into the worksheet.

Answer 1

Hi,

assuming that you want the picture

in sheet1 tab and in range (Target) A1:D10

1. SaveAs your workbook with extension .xlsm (macros enabled)
2. right click on sheet1 tab, select view code and paste in the following:

Private Sub Worksheet_Activate()

Set Target = Range("A1:D10") '<<< change target rangeOn Error Resume Next

Dim v As Variant

Dim p As Picture

Application.ScreenUpdating = False

For Each p In ActiveSheet.Pictures

If Not Intersect(p.TopLeftCell, Target) Is Nothing Then p.Delete

Next

v = "http://www.desicomments.com/wallpapers/force_of_nature/force_of_nature_33.jpg"

Set p = ActiveSheet.Pictures.Insert(v)

With p

.Height = Target.Height

.Width = Target.Width

.Top = Target.Top

.Left = Target.Left

End With

Application.ScreenUpdating = True

End Sub

3. press Alt+Q to close Visual Basic

Answer 2

Hi Scott!

I made you an example excel. https://www.dropbox.com/s/cwa7pimpf79t38h/example.xlsx

So when you change the red number e.g. to 2. The pic would be there automatically.

This almost does the trick but not quite, because the link is not variable:

Private Sub Worksheet_Activate()

Set Target = Range("B8:I19")

On Error Resume Next

Dim v As Variant

Dim p As Picture

Application.ScreenUpdating = False

For Each p In ActiveSheet.Pictures

If Not Intersect(p.TopLeftCell, Target) Is Nothing Then p.Delete

Next

v = "http://www.captainsshop.fi/verkkokauppa/images/73169ce4a606c14fb49be1c6e64c043d_1103_df1.jpg"

Set p = ActiveSheet.Pictures.Insert(v)

With p

.Height = Target.Height

.Width = Target.Width

.Top = Target.Top

.Left = Target.Left

End With

Application.ScreenUpdating = True

End Sub

Answer 3

@JohnnieSimon - Can you explain a little more about your variable link?  Does the link/picture change based on a result?  For example if the result of A2+B2 is 4 then you want one link/picture, but if it is anything else then you want another link/picture?  Or is the change based on an action, such as double-clicking on a cell to get a link based on where you click?

Answer 4

Nice.. that worked fine. But I have a different problem to the same topic.

My link is variable. I'm using HLOOKUP from another sheet so only change is that I want the pic opened from a cell, not from a link that is always the same.

I have the cell already linked from another sheet (to the sheet that has the code) so I want the variable pic to be visible only from a hyperlinked cell. By changing link to cell doesn't work.

Thank you..

Answer 5

Thank you very much - I will give this a try and see how it goes.