Hover over cell to display a NON comment dynamic external image

[spongebob]

Hello All,

I have seen a few things referencing a comment and filling it with a picture, but that unfortunately isn't dynamic.
Then I have seen some outdated vba code (just older posts on the web) showing a way to accomplish it, but not finding one that works.

What I would like to do is define a folder name, then grab values from a column and then from each cell, display an external image.

An example would be:
Column A has Pic name, Column 2 shows the year it was made.
In the first data row, you have the name of the picture, so the path would be built like concatenate("c:\common-Path\",a2,".jpg")
In this example it's 1.jpg
Now if I hover over the one, I would like to display the image.
Any suggestions appreciated!

PIC#	Year
1​	2005​
2​	2019​
3​	2005​
4​	2009​
5​	2005​
6​	2005​
7​	2016​
8​	2016​
9​	2016​
10​	2010​
11​	2010​

 

[spongebob]

I was able to figure out the details and it's working for hiding the image, but the dynamic row location for the image isn't working well.
Basically if you have an image thats lets say 30 rows tall, within the first 8 rows it looks good, but rows 10+ the bottom of your image starts to go beneath the bottom of the viewable spreadsheet area.
If you scroll down, you see the image well, but click on a lower rows image references and you end up with only the very top of the image showing, an example.
In the example, I'm hovering over row 30 ( 40 rows visible )

Thank you!!!!

 

[edmitchell]

Hi, code amended such that the image now (should!) display from the 1st visible row downwards.

Cell	Contents
E1 (e.g. an unused cell)	=Get_IMAGE(E2,H1:P25) E2 , E3 as before

Revised Code

Public Function Hover(C As Range)
'C is the name of the Image file
Dim Our_Sheet As String, New_Image As String

    Our_Sheet = C.Parent.Name 'what is the name of the Sheet where we are?
   
    New_Image = "C:\temp\pictures\" & C.Value & ".jpg" 'Default folder, and all Images have an "JPG" extension
    
    'If the "check" cell already has the value we are potentially changing to, then do nothing (as nothing has changed!)
    If (New_Image = Sheet1.Range("e3").Value) Then
    Else
        Sheets(Our_Sheet).Range("e2") = New_Image  'set cell "E2" to the location & name of the new image to display..
                                        '"E2" is referenced in the formula in "E1" which contains the reference to the Get_Image function
    End If
    
End Function

Public Function Get_IMAGE(FilePath As String, Location As Range)

'Paramaters are:-
' FilePath = location of the reference to the new image (full path, file name, and extension)
'Location = Range where we are to display the image

Dim Our_Sheet As String, Image As Shape, Name_of_Image As String, Top_left As Range, Top_Row As Integer
Dim Check_Range As String, I_Sep As Integer, I_Dollar As Integer

Name_of_Image = "Image"
Our_Sheet = Location.Parent.Name

'If it exists remove the current picture
For Each Image In Sheets(Our_Sheet).Shapes
    If Image.Name = Name_of_Image & "1" Then
        Image.Delete
    End If
Next Image

'Find the first visible row in our Window
Top_Row = ActiveWindow.VisibleRange.Row

'Now adjust Location (from Function Parameters) to be in our current Window
'Find Range separator
I_Sep = InStr(Location.Address, ":")
I_Dollar = InStr(2, Location.Address, "$")
Check_Range = Left(Location.Address, I_Dollar - 1) & "$" & Top_Row & ":" & Mid(Location.Address, I_Sep + 1, 99)

Set Top_left = Range(Check_Range)
Top_Position = Top_left.Top 'in Points...

'Add Image in the right location
Set Image = Sheets(Our_Sheet).Shapes.AddPicture _
(FilePath, msoFalse, msoTrue, Location.Left, Top_Position, -1, -1)

'Resize Image if needed
If Location.Width / Location.Height > Image.Width / Image.Height Then
    Image.Height = Location.Height
Else
    Image.Width = Location.Width
End If

'Update the name of the image (which we will used next time function runs)
Image.Name = Name_of_Image & "1"
Image.OnAction = "Image1_click"

Get_IMAGE = "IMAGE: "

End Function

 

[spongebob]

Thank you!

I added in the function for the click to remove the image that was omitted in this version.

Sub Image1_Click()
    Dim Our_Image As Shape, Our_Sheet As String
   
    Our_Sheet = Application.ActiveSheet.Name
    Set Our_Image = Sheets(Our_Sheet).Shapes("Image1")
    Our_Image.Delete
   
End Sub

 

[MaykelKastelijn]

Hi,
I am looking for exactly this function. I also tried an older VBA solution but the image keeps flickering by being reloaded. Your solution seems to tackle that problem. But I have a problem I can's seem to get arround after 4 hours of trying. I'm not that much into VBA I admit but I can't figure out that goes wrong when I follow your instructions.
Somehow I can't get the first part running (Hover). It never results for the New_Image value to produce an URL. It keeps ending up with a name error.
The part for the Get_Image works, I've tried it with a temporary manualy added link for E2.
To start at the basiscs I used the first post and filled in the values, I will change to the latest when the first part work (as I like to try and test what happens with each change).
To test I have placed two jpg images (1.jpg and 2.jpg) directly in the E: root (and adjusted this in the VBA script).
Any ideas what I am doing wrong here?

 

[MaykelKastelijn]

Sigh....
Never mind. Chatted with a friend who gave me a suggestion...
It seems that in Dutch some commands are also translated and the English versions are not working so I had to use "=Als.Fout" instead of "IFERROR".... All works now as it should!
Same goes for a comma that must be replaced by ; to function...
Thanks for the code anyway!!