Inserting a chart instead of text into comment box

Ashleigh K.

unread,

Oct 6, 2003, 2:12:46 PM10/6/03

to

Hello All,

I am trying to manipulate the "insert comment" capability in excel. Instead
of having text appear in the comment box when the cell is selected, I would
like a pie chart to appear. I know that it is possible to insert a picture
into this comment box, but I need to find a way to insert the chart without
pasting it as a picture. That is, the chart needs to remain dynamic. I would
appreciate any input anyone might have on how I might accomplish this.
Thanks in advance,

Ashleigh

Jon Peltier

unread,

Oct 7, 2003, 1:40:44 PM10/7/03

to

Ashleigh -

Hmm, I didn't know you could insert a picture into a comment. I know
what I'll be playing with later.

Use the methodology shown by John Walkenbach's example, How to Put a
Chart Onto a User Form (look under Developer Tips at http://j-walk.com).
He shows how to save a chart as a gif file, then load the gif file
into a picture control on a form. What you might do is copy the chart
as a picture, and paste it in the comment. I'm not sure how you'd make
it dynamic, though. I couldn't find a Comment_Show event you could do
this in response to.

But you could set up a Worksheet_SelectionChange event procedure, so if
you click in a new cell (not just mouse over it like a comment), you can
have the macro retrieve or draw a chart, and temporarily show it.

Another trick I just thought of is to display the chart window. The
chart itself has to be on the active sheet, but anywhere on it. You can
run this macro from a Selection Change event, and it will open the chart
window for the indicated chart, and line it up with the active cell:

Sub ShowChartWindow()
Dim atop As Single
Dim aleft As Single
atop = ActiveCell.top
aleft = ActiveCell.left
Worksheets(1).ChartObjects(1).Activate
ActiveChart.ShowWindow = True
ActiveWindow.top = atop
ActiveWindow.left = aleft
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Debra Dalgleish

unread,

Oct 7, 2003, 2:40:47 PM10/7/03

to

Jon, there are instructions here for adding a picture to a comment:

http://www.contextures.com/xlcomments02.html#Picture

I don't know how you'd make it dynamic, but you could use a macro to
save a gif file of the chart, and automatically update it after any
changes to the data.

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Jon Peltier

unread,

Oct 8, 2003, 12:57:23 PM10/8/03

to

Deb -

I thought the Chart Window approach was kind of cool.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Debra Dalgleish

unread,

Oct 8, 2003, 2:19:11 PM10/8/03

to

Jon,
Yes, the Chart Window approach definitely is cool!

I was just responding to the first sentence of your post -- "I didn't
know you could insert a picture into a comment" -- to save you some
experimenting time.

Deb

Jon Peltier

unread,

Oct 9, 2003, 5:25:49 PM10/9/03

to

Who are you to cut into my quality goofing off time?

Debra Dalgleish

unread,

Oct 9, 2003, 6:13:50 PM10/9/03

to

Sorry! I don't know what I was thinking. I'll bet you could spend lots
of time goofing off here:

http://www.geocities.com/jonpeltier/Excel/Charts/index.html

and learn a thing or two about charts!

--