Excel can I link to a picture in another sheet, same worksheet?

[jkanderson]

I know I can put pictures in an Excel comment.
Sometimes I want to use the same picture on several different sheets in the same workbook.
I want the picture to popup on hover over like a picture in a comment does.
Is there any way for the comment to link to (and show) the picture by hyperlink?
I want to keep filesize down and rather than insert the same picture 3 and 4 times on different sheets I am hoping a comment can link to the (one) original picture on the sheet
If this can be done how can I do it?

 

[Yongle]

Can I send you a simple 10 row or so example of what I am trying to do? Perhaps an example of members of Britain's Royal Family? That would make things a little easier to follow

The tool to use is provided on this site. It enables you to upload a sample range very easily.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[jkanderson]

Not sure I understand the sample range. My worksheet has the sheet of names, this is the main one. Each name contains a comment with fastc of the person. In the cell just to the left of the name cell is a comment with picture only. No code put in yet.

The light bulb did come for the picture sheet, as I don't need one. Once a picture in a comment is in a cell on the name page, I can just copy and paste the comment wherever I want as many times I want.

QVic pic (actual photo in a comment) - Queen Victoria (text facts in a comment) - Vic's father Prince Edward (text facts in a comment) - Vic's mom Victoria (text facts in a comment - the next two cells to the right of the parents (not shown here) will each have a comment copied picture in a comment

The problem I see is file size. With just a few names there is no issue. With several hundred, there might be. Unless, can the code copy the comment to another cell "on the fly"? That is, copy right on mousein (or mouseover). Then delete on mouseout. If I want to go back and look at it again, then of course it would require another copy to the cell when I mouseiover All the names of the people will have the actual picture in a comment. It is the pictures of the parents and perhaps the kids that will have a copied picture in a comment.

Q.Vic.pic

Queen Victoria

Prince Edward (Duke of Kent)

Princess Victoria of Saxe-Coburg-Saalfeld

 

[Yongle]

Have you tested code provided in post#10?

 

[jkanderson]

Tried to, but as I am very new to this, I can't seem to get the macro named. Does the "VBA Code" at the very beginning of the code in Post 10 also go into the copy and paste to the module?

 

[Yongle]

Have you tested code provided in post#10?

Tried to, but as I am very new to this, I can't seem to get the macro named. Does the "VBA Code" at the very beginning of the code in Post 10 also go into the copy and paste to the module?

Try in a NEW workbook
- install the code as instructed below
- insert a comment in last visible row in column A in Sheet1
- double click on same cell to hide comment
- right-click on same cell to make comment visible

The code
The code must be placed in the sheet module (it will not work if placed anywhere else anywhere else)
- right-click on Sheet1 name tab \ select View Code \ paste code into that window

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    On Error Resume Next
    With Target
        If Not .Comment Is Nothing Then
            Cancel = True
            Application.ScreenUpdating = False
            With .Comment
                .Visible = True
                .Shape.Top = ActiveWindow.VisibleRange.Top + 10
                .Shape.Left = Target.Offset(, 2).Left
            End With
        End If
    End With
    On Error GoTo 0
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    On Error Resume Next
    Cancel = True
    Target.Comment.Visible = False
    On Error GoTo 0
End Sub

 

[jkanderson]

I was following a link I found on the web for beginners to insert code into Excel. It didn't mention anything at all about right-clicking on the sheet/name tab to bring up the module. So I am glad you included this, very helpful! I took Basic many years ago, and tried html so know a little about coding, but not enough for something like this. After I copied and pasted the code into the module (my new worksheet has only the one sheet) don't I have to save the worksheet before going any further? The link said to save as macro-enabled worksheet then to name the macro then Control/S. That was confusing -

 

[Yongle]

When a workbook contains macros it must in future be saved as macro enabled (and will have extension .xlsm instead of .xlsx)

Did the code in post#15 work for you ?

 

[jkanderson]

Sorry, I thought I posted that the code did work. I copy/pasted, went back to sheet 1 to try it.

The hover-over still popped up the comment as I hadn't saved the workbook yet.

Then I right double clicked and the comment popped up two columns over at the top of the sheet and it stayed there.
A left double click made it disappear.

So the code works so far.

But what I am getting at;
1. I can save the sheet by clicking on the normal file/save
2. Then I choose as macro enabled and it does have the extension of .xlsm.
3. After that things get fuzzy.
4. Double clicking on the xlsm sheet comment does nothing.
5. Don't I also have to name the macro while still in the view source module?
6. If not how does the code know to run?