Solved: Convert chart into a insert comment picture

[Shazam]

Hi everyone,


Right now I use a code to convert a chart into a Jpeg and save it onto my C:\Drive and then manually insert a comment in a cell along with that picture chart. Is there a easier way to convert the chart on the worksheet into a insert comment picture and deleting the existing chart?

I left a sample workbook below. Look at worksheet tab "Expected Result" thats how it should look after running the macro.

Is this possible?

[Paul_Hossler]

Here's one way

Paul



Option Explicit
Sub Chart2Comment()
Const sFile As String = "##Chart##.gif"
Dim rDest As Range

On Error Resume Next
Kill sFile
On Error GoTo 0

Call ActiveSheet.ChartObjects(1).Chart.Export(sFile, "GIF", False)

On Error Resume Next
Set rDest = Application.InputBox("Select the cell and 'OK', OR 'Cancel'", "Chart2Comment", , , , , , 8)
On Error GoTo 0

If rDest Is Nothing Then
Kill sFile
Exit Sub
End If


With rDest.Cells(1, 1)
If Not .Comment Is Nothing Then .Comment.Delete
.AddComment
.Comment.Visible = False
.Comment.Shape.Fill.Transparency = 0#
.Comment.Shape.Fill.UserPicture sFile
End With

Kill sFile

End Sub

[lucas]

That's a pretty slick one Paul..

[Shazam]

WOW that is great thank you very very much Paul! One thing though instead of having a input box can the insert comment picture go directly to cell B2? Also how to change the size of the insert comment?

[Shazam]

Ok I think I got.

Set rDest = Range("B2")

Once again thank you for the help.!

[Paul_Hossler]

BTW, I used Application.InputBox (slightly different from the VBA InputBox) so I could set the input type = 8 (Reference) so that I could click the cell to receive the Comment; didn't feel comfortable hard coding B2

Paul

[JonPeltier]

Use this to avoid the need for the input box:


Set rDest = ActiveCell

[Shazam]

Hi Paul,


Is there a code that will only print out the insert comment picture on a full landscape?

[Paul_Hossler]

Not that I know of, but maybe you could tie into the Workbook_BeforePrint event, and hide/show comments depending on landscape setting

I can experiment if you want

Paul

[Shazam]

Not that I know of, but maybe you could tie into the Workbook_BeforePrint event, and hide/show comments depending on landscape setting

I can experiment if you want

Paul


Ok Let see how that works.