How do I (or can I) add a chart within a comment?

[GeneW]

I want my chart (very small chart) to emerge when you hover over a sales
number.
As if the chart was embeded within a comment.
When you go over a cell with a comment, it opens up.
I do not want the chart or graph to be present on other pages or tabs.

 

[GeneW]

Your directions for embedding a chart to a comment was great.

Do you have any thoughts on how I can have a real-time chart open and close
so it would be attached to a cell?
I am flexible on was to do this, but I have an excel spreadsheet and I want
peoples sales numbers to be on the sheet but not always open large pictures.
I would like them to work very similar to the way comments pop up.

Also, would you know how I could go about making this suggestion to
Microsoft if there is not a way for this to work?

Thanks in advance for your help.

 

[GeneW]

Your directions for embedding a chart to a comment was great.

Do you have any thoughts on how I can have a real-time chart open and close
so it would be attached to a cell?
I am flexible on was to do this, but I have an excel spreadsheet and I want
peoples sales numbers to be on the sheet but not always open large pictures.
I would like them to work very similar to the way comments pop up.

Also, would you know how I could go about making this suggestion to
Microsoft if there is not a way for this to work?

Thanks in advance for your help.

 

[Andy Pope]

You would need to run code every time the chart data changed.
When you say the chart is real-time do you mean the numbers are constantly
changing or that they are updated 1 a day/week/month?
Which ever you will need to run some code.
So here is some code to place the image in a comment. Just change the
chartobject and cell reference.

'--------------------------
Sub Test()

m_MakeCommentChart ActiveSheet.ChartObjects(1), Range("D3")

End Sub

Sub m_MakeCommentChart(Cht As ChartObject, Rng As Range)

Dim strTempFile As String
Dim objComment As Comment

On Error GoTo ErrMakeCommentChart

strTempFile = ThisWorkbook.Path & Application.PathSeparator & "xyz" &
Format(Now(), "yyyymmddhhmmss") & ".gif"

Cht.Chart.Export strTempFile
Set objComment = Rng.Comment
If objComment Is Nothing Then
Set objComment = Rng.AddComment
End If
objComment.Shape.Fill.UserPicture strTempFile

Kill strTempFile

ErrMakeCommentChart:
Exit Sub

End Sub
'--------------------------

You could try your suggestion here, but don't hold your breath.
http://office.microsoft.com/en-gb/suggestions.aspx?sitename=CL100570551033&type=0

Cheers
Andy

 

[Peter T]

I'm pretty sure there's no direct way to display a dynamic chart in a
Comment or similar. Even VBA code cannot track "mouse over" cells (at least
not directly) and in turn toggle visibility of a dynamic chart in the same
way Comments are displayed.

In theory VBA code could update the picture in the comment as changes are
made to the underlying chart data in cells. Quite a lot of work and possibly
mountainous to ensure all works reliably.

Not quite what you are looking for but you could make a dynamic chart
visible when you "select" a particular cell (or say a cell within a given
range of cells), then when user selects some other cell hide the chart. This
requires VBA "event" type code. Could also ensure that if user attempts to
select the visible chart the selection will revert to the cell.

If interested in the "select cell and display" approach and able to adapt
code to your own requirements post back.

Regards,
Peter T

Post back if that's

 

[GeneW]

Peter-
Thanks for the feedback. Although you maybe on the right course. I think
that is WAY above my head.
I would love to be able to try what you posted, but I have NO experience in
Excel code.

Gene

 

[GeneW]

Andy-
Great feedback.
While I would love to try to adapt my sheet with what you expailed. I do not
have an ounce of experience in Excel Code. I think it would be way over my
head, and quite honestly not that appreciated by my company for the time
spent to accomplish the task.
Thanks for your help. I think it would be a great add during an upgrade for
excell.

Gene

 

[Jon Peltier]

Do your charts necessarily have to be "large" pictures? If you simplify the
chart, remove some of the labels and other excess shapes in the chart, you
might be able to show what you want with a chart a few cells wide and half a
dozen cells tall. In the extreme you could even try sparkline charts:

http://sparklines-excel.blogspot.com/
http://www.bonavistasystems.com/
http://www.bissantz.de/sparklines/

- Jon