How to Display a changing Camera Icon-Picture Link in Cell Comments

[all4excel]

Is it possible to display an Excel generated Image i.e. Camera Icon-Picture Link into a comment..

The Camera Icon-Picture Link is connected with actual Data and keeps on changing each time the data is changed...

But I want to tap this feature of the Picture Link and add it to a comment so that I will have a Comment which is connected to Actual Data..

<TABLE style="WIDTH: 244pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=324><COLGROUP><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2592" span=4 width=81><TBODY><TR style="HEIGHT: 24pt" height=32><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 61pt; HEIGHT: 24pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl101 height=32 width=81></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 61pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl102 width=81>Col A</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 61pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl102 width=81>Col B </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 61pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl102 width=81>Col C</TD></TR><TR style="HEIGHT: 23.25pt" height=31><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 23.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl103 height=31>Row 1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: black; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl104>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl105>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl106>3</TD></TR><TR style="HEIGHT: 23.25pt" height=31><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 23.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl103 height=31>Row 2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: black; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl107>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl108>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl109>6</TD></TR><TR style="HEIGHT: 23.25pt" height=31><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 23.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl103 height=31>Row 3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: black; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl110>7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl111>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl112>9</TD></TR><TR style="HEIGHT: 24pt" height=32><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 24pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl103 height=32>Row 4</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: black; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl113>10</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl114>11</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl115>12</TD></TR></TBODY></TABLE>

This is the Actual Data and the Camera Icon-Picture Link has the same Image and now I will be programming this data to kepp on changing based on certain condtions..so naturally this Camera Icon-Picture Link below will keep on changing..

I need to get this Picture Link Image in the comment so that it I have the evre-changing data in a comment too..

As each comment can be programmed seperately..

Regards

 

[all4excel]

Appreciate your efforts but still not getting what I want..

I will upload my File to explain the requirement..

NOw thanks to you I too created an account and have uploaded the file with explanations in the comments..

In order to send you the path, which path do I need to provide?http://www.mediafire.com/myfiles.php

 

[all4excel]

Please find the link,

GOt this too!

http://www.mediafire.com/file/auiiptwfx0voboq/MickG.xls

 

[all4excel]

Hope this is possible..!

And not that I have hit a ROck...

How to save the Picture Link image Temporarily and then again call it in comments?

 

[MickG]

Try this:-
When the sheet Calculates the Range "A1:C4" is saves as a "gif" file, Alter
Path in code to suit.
The Range Picture from the "gif" file is then is then used to fill the comment in "A8". (Alter range to suit)

Private Sub Worksheet_Calculate()
Dim rng As Excel.Range
Dim cht As Excel.ChartObject
' Change Path to suit !!
Const strPath As String = "C:\Documents and Settings\test\Desktop\PictureTemp.cam.gif"
Application.ScreenUpdating = False
 Set rng = Range("A1:C4")
  rng.CopyPicture xlScreen, xlPicture
   Set cht = ActiveSheet.ChartObjects.Add(0, 0, rng.Width + 10, rng.Height + 10)
     cht.Chart.Paste
      cht.Chart.Export strPath
        cht.Delete
'Change Range to suit!!
With Range("A8")
 If Not .Comment Is Nothing Then .Comment.Delete
     .AddComment
     .Comment.Shape.Fill.UserPicture strPath
     .Comment.Visible = True
     '.Comment.Shape.TextFrame.AutoSize = True
 End With
Application.ScreenUpdating = True
Set cht = Nothing
Set rng = Nothing
End Sub

Mick

 

[all4excel]

Dear MickG.

This is exactly what I wanted ...

Kudos to you MickG...!

But the Comments size is too small and the other thiing is that I want the entire comment to be filled with the Image and not have any White portion...
The Size of the comment can be the same like the cells Size so that the data will be viewable and it should not display the white portion but have clean edges just like a normal picture would have if Inserted in the comments..

SO can that be countered too!...

I think you are just amazing..Man

 

[MickG]

Don't think you can get rid of the white border line ,but add lines below (Red) and adjust values to suit, for overall size.

With Range("A8")
 If Not .Comment Is Nothing Then .Comment.Delete
     .AddComment
     .Comment.Shape.Fill.UserPicture strPath
     .Comment.Visible = True
     .Comment.Shape.Height = 120
      .Comment.Shape.Width = 170
End With

Mick

 

[all4excel]

Dear MickG,

Is the Size so due to the Chart Object being used, using the same way can we have the Image exactly of the same size as the selected range of cells...

When we use the Camera Icon it jsut replicates the Entire Range includingt he Size and the formatting so can that be included in your method too...

I tried this in the code to remove the White Chart Portion:

Set cht = ActiveSheet.ChartObjects.Add(0, 0, rng.Width - 10, rng.Height - 10)

It worked but still shows the distinct white edges..

 

[all4excel]

Hey MickG,

Wow thats Awesome Man,

So Crisp and Compact code to get the Picture Moving Comment..

Great..

Thanks a Million ..

How do I Add Reputation...? for You and also mark this as SOLVED

Regards
all4excel

 

[all4excel]

Can we get the Path automatically ny using the below:

Dim Path As String
Path = ThisWorkbook.Path & "PictureTemp.cam.bmp"
' Change Path to suit !!
Const strPath As String = Path

 

[MickG]

As long as folder "PictureTemp" is at the end of the Workbook path !!
Perhaps syntax more like this:-

ThisWorkbook.Path & "\PictureTemp\cam.gif"

Mick