Camera Tool Image in Comment Box

[WLM1976]

Hi All,

I am new to here but impressed with the level of knowledge shown and would appreciate some help I possible.

I am trying to attach an image to a comment so that it will show when you hover over the cell.
I know this can be done with saved image files from defined paths, and here's the curve ball, can this be done to pick up an image that is in another work sheet within the active work book?

I have considered other options, such as using macros and command buttons to get the image, but because of the demands from the end users having a roll over comment box would be by far and away the best option.

Any help is much Appreciated.


Thanks,

 

[MickG]

This code will copy the range as a picture (Camera tool) place on "Chart Object" and export The file, to Your "Workbook Path" then recall it to a Sheet comment as required.

Dim TempCht As Chart
Dim Rng As Range, w, h
Dim fname As String
Set Rng = Range("A1:D6")
Rng.CopyPicture
  w = Rng.Width
  h = Rng.Height
    ActiveSheet.ChartObjects.Add Left:=200, Top:=50, Width:=w, Height:=h
    ActiveSheet.ChartObjects(1).Activate
    ActiveChart.Paste
    fname = ThisWorkbook.Path & "\temp.gif"
    'MsgBox fname
    ActiveChart.Export Filename:=fname, filtername:="Gif"
With Range("A12")
        On Error Resume Next
            .comment.Delete
        On Error GoTo 0
            .AddComment
            .comment.Shape.Fill.UserPicture fname
            .comment.Shape.Width = w
            .comment.Shape.Height = h
End With
ActiveSheet.ChartObjects(1).Delete

 

[WLM1976]

Works perfectly, Many Thanks.
I have one scope creep on this though.......
As the report is dynamic, and the image will change depending on selected week, is there a way to include in the code that the macro will run when a specific cell, say A3 is changed?
Also I need 12 of these to run when the week number is updated so any help would again be much appreciated.

 

[MickG]

If that is 12 different ranges and 12 different comments, could you give an example of the range addresses and the comment cells addresses.

 

[WLM1976]

Hi Mick,

Yes it is 12 different comments. Essential (without sharing Company Data), what I am trying to achieve is an automated dashboard that will show the breakdown of the number within a specific cell. eg. cells G32:R32 show Total Travel Costs by Cost Centre, what we want to achieve is by rolling over each of these cells the comment window pops up with the breakdown of the numbers that make up the total amount e.g. G23 = £5,200 - roll over triggers comment box showing >Air fares £5,000 - Car Hire £200.

Therefore each range is specific to each cell, I can get the code in to run them all but ideally this is to be automated when the week number is changed.

 

[MickG]

In order to write some code , I need to know the range addresses of the 12 ranges and the range addresses of the 12 cells with comments, relating to them. If you can supply those "Addresses", I will try to write some code for you.