The steps to do this manually follow:

1. Create and/or save the picture image you want the comment to display.

2. Create the comment, if you have not already done so, and select the cell in which the comment is.

3. From the menu bar, click Insert, Edit Comment, or right-click the cell and select Edit Comment.

4. Right-click on the comment border and select Format Comment.

5. Select the Colors and Lines tab, and click the down arrow belonging to the Color field of the Fill section.

6. Select Fill Effects, then select the Picture tab and click the Select Picture button.

7. Navigate to your desired image, select it, and click OK twice.

The effect of having a "live chart" in a comment can be achieved if, for example, the code is part of a SheetChange event when the chart's source data is being changed. Also, business charts are updated often so you might want a macro to keep the comment updated and to avoid repeating the same steps. The following macro does just that: modifies the macro for file path name, chart name, destination sheet, and cell, and size of comment shape, depending on the size of the chart:

Sub PlaceGraph()

Dim x As String, z As Range

Application.ScreenUpdating = False x = "C:\Excel VBA 2003 by Jelen & Syrstad\Graph.gif"

Set z = Worksheets("Sheet1").Range("B10")

On Error Resume Next z.Comment.Delete On Error GoTo 0

ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.Export x

With z.AddComment With .Shape

.Height = 322 .Width = 465 .Fill.UserPicture x End With End With

Range("A1").Activate Application.ScreenUpdating = True

0 0

Post a comment