View Full Version : Solved: Convert chart into a insert comment picture
Shazam
04-27-2007, 12:53 PM
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
04-27-2007, 06:12 PM
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
04-27-2007, 06:24 PM
That's a pretty slick one Paul..
Shazam
04-27-2007, 07:09 PM
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
04-28-2007, 11:39 AM
Ok I think I got.
Set rDest = Range("B2")
Once again thank you for the help.!
Paul_Hossler
04-28-2007, 05:06 PM
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
04-28-2007, 05:55 PM
Use this to avoid the need for the input box:
Set rDest = ActiveCell
Shazam
04-28-2007, 05:56 PM
Hi Paul,
Is there a code that will only print out the insert comment picture on a full landscape?
Paul_Hossler
04-29-2007, 07:30 AM
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
04-29-2007, 10:16 AM
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.