pop-up in charts

acl · Jun 28th 2004

Hi

I am basically brand new to VBA and excel macros...so please keep that in mind. If the solution lies in either of those, if you would be kind enough to show me that would be greatly appreciated. I am just creating a bar graph with some values. Is it possible to display a comment or text from a cell if I mouse over or click one of the bars? I was basically looking for a popup like a comment.

More importantly, is it possible to create a pop-up of some sort when a bar is clicked, so it displays text from different cells combined. Like under column A on the spreadsheet I have values in A1, A2, A4, A5, representing titles. Then there is another column corresponding to each cell, in maybe E1, E2, E4, and E5, outlining a description. The popup would then pop up and display the title followed by a : and then the description, in paragraph format. So for instance the popup would display:
------------
|title(A1): description(E1)
|title2(A2): description(E2)
--------------

etc.
If a popup is not viable then a display mechanism like a comment would also work.
I hope it is clear what I am trying to do. Please take notice that I need each bar to display text in certain cells, each varying from bar to bar.

Thanks in advance
acl

Jong · Jun 28th 2004

help

Hey Acl,

I had a similar problem with my current project. It seems the only way to override the mouse click even in the Chart is to create an image of the Chart, and map the image to the mouse button clicks.

After realizing that the solution to my problem would require such a long resolve time, I choose not to do this.

There is however a popup that will appear via Excel events when you use "Mouse Over" chart points.. It will display the Point Name, Point Value, and Point Xvalue. This is done automatically by excel..

If you wish to convert your chart into an image, and map it, here is some code to hlep you get started. To use the following code, you must first add reference to "Microsoft Scripting Runtime" in Tools > References:

Code

'ChartName is the name of the worksheet containing the chart
Function SaveChartAsGIF(ChartName)


    Dim FName As String
    Dim RepString As String
    Dim mFileSysObj As New FileSystemObject
    
    'select chart, assume it is the only chart on the sheet
    Worksheets(ChartName).Activate
    ActiveSheet.ChartObjects(1).Activate
    ActiveChart.ChartArea.Select


    'create Charts directory in current workbook location
    FName = ThisWorkbook.Path & "\Charts\" & ActiveChart.Name & ".gif"
    
    If mFileSysObj.folderexists(ThisWorkbook.Path & "\Charts\") = False Then
        Call mFileSysObj.CreateFolder(ThisWorkbook.Path & "\Charts\")
    End If
    
    'Export the Chart as a GIF 
    ActiveChart.Export filename:=FName, FilterName:="GIF"
    
End Function

Display More

The above code will generate a GIF of your chart, and save it in /Charts directory where your workbook resides on your HD.

acl · Jun 28th 2004

Hey Jong

Thanks for the reply, but there are still a few things that are cloudy to me, or rather, a lot of things.

Perhaps my explanation was unclear, but what I was looking for was an actual pop-up that can display a lot of formatted text, whereas a comment box can display so much.

You had suggested that I make the chart an image, and had generously supplied the code as well =]. Thanks, but I don't see how I can use the image, not to mention that I don't know how to make the image even with the code you have provided (I am new to macros,vba). Even with an image, I had wanted to had each bar in a bar graph display different text as a popup from various cells, and I'm not sure how an image can help. Is this what mapping is for? If so, please guide me through how to make the chart an image and map it. lol sorry as you can see I am pretty (as in completely) new.

Thanks for replying!
acl

Jong · Jun 28th 2004

solution

Regardless of the solution, this is not an easy task. If you are unfamiliar with macros, it would be quite a good time to learn

What an image can do, is if you setup an IMAGE MAP over the image, when you click on certain positions (X,Y coordinates) of the image, you can raise specific events, such as displaying a Comment Box of some sort.

This method is however useless if your charts are dynamic. If you map the image for certain x,y coordinates, and the chart were to change, you would need to re-map the entire "clickable" points.

Other than that, I haven't been able to find any other way to capture mouse clicks on a chart. Excel has a built in mouse event when you click on a chart, and as far as i know, they cannot be overriden.

Sorry I couldn't help out much.

An alternative solution could be to use the cell that is linked with the chart to display the comment.

Anonymous · Jun 28th 2004

What you want to do isn't easy, but in addition to Jong's suggestion go to http://www.tushar-mehta.com/
and select Hover Chart Label to download something that may help.

acl · Jun 28th 2004

ok thanks for all the replies.

I'll try and familiarize myself with macros

also I ran across something else. I was thinking of creating autoshapes and then attaching comments and a cell reference to is. Is there some code that can draw up a popup when the button is clicked?

Anyways yea I need to go learn me some macros =] Thanks a lot.

acl

pop-up in charts

Participate now!

Share