?
Solved

XYChart interactive points/markers

Posted on 2011-10-02
13
Medium Priority
?
397 Views
Last Modified: 2012-05-12
Dear experts,

I  have an XYChart with only 30 points plotted on it.I want by using an auto run macro to be able to click on point/marker then a message box will pop up showing the series name or the x and Y valuesor even a value from a cell i define or to do any other event.
I tries to use the below command in my macro but no use.
if Worksheets(2).ChartObjects(1).Chart.SeriesCollection(1).Points(1).Selection = True then ....

please help
0
Comment
Question by:bsaisu
  • 6
  • 4
  • 3
13 Comments
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36900214
OK - this is not very simple.  You need to define an event handler for events for your chart. http://msdn.microsoft.com/en-us/library/bb211434(v=office.12).aspx tells you how to do this.

I have done it here in a simple example.  I have a sheet1 with an x-y scatter chart.  I created a class module, ChartEvents, containing this code
Option Explicit

Public WithEvents objChart As Excel.Chart


Private Sub objChart_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)

    Dim s As Excel.Series
    Dim p As Excel.Point
    
    If ElementID = xlSeries Then
    
        Set s = objChart.SeriesCollection(Arg1)
        If Arg2 > -1 Then
            Set p = s.Points(Arg2)
            MsgBox s.Name & vbTab & s.XValues(Arg2) & vbTab & s.Values(Arg2)
        End If
    End If
    
End Sub

Open in new window

The key thing here is the public withevents declaration, which allows you to trap events on the chart.  You then need to assign your chart to that variable, which I have done in the activate event for Sheet1.  You could also do this in a Workbook_Open event if your chart already exists.  So the code in the Sheet1 module is  
Option Explicit
Private mobjChartEvents As ChartEvents

Private Sub Worksheet_Activate()
    Set mobjChartEvents = New ChartEvents
    
    Set mobjChartEvents.objChart = Me.ChartObjects(1).Chart
    
End Sub

Open in new window

This just assumes your chart is the only chart on sheet1 - you could give it a different chart if you want. I've uploaded the whole file.  This is obviously very basic - you can elaborate on it as much as you  like.
chartevents.xlsm
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36900216
Having said all that, of course I'm sure you know Excel will show you the series name and the x and y values if you just hover over the point with the mouse
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36900553
That's a good one!  

Another alternative with some flexibility around when you want to be prompted with the X,Y values.  One of the earliest E-E solutions I worked on:  http:/Q_26698928.html.

The goal in this solution was associated with having many X,Y points together, so the user would select one, then use the arrow keys to "cycle" to the point of interest.  Upon hitting INSERT, the user would be prompted with the X,Y point...

Scroll down to the solution and download the file.

Things start rolling when you select the chart sheet, so if the chart sheet is up, go to another tab, then come back to the chart.  Select any point on the chart, then hit the INSERT key and you'll be prompted with X,Y coordinates.

This solution is setup for a chart sheet, but if you prefer it, I can assist in adopting to an embedded chart.

Cheers,

Dave
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 

Author Comment

by:bsaisu
ID: 36901934
andrewssd3,

Thank you for your comprehensive answer. It is very useful but I still have an issue. I have taken your attached .xlsm file then I have re-saved it as an .xls file because I am still working on Excel 2003. Then I added an auto macro to work when the file is open to activate the event on sheet 1. Otherwise I have to open the VBE and run it manually. My problem is really not here. The problem that I have to click on the point, wait for a second then click again and the message box would appear. I don't know why it is not working from the first click??!! Is it because of Excel 2003 ??!!
I have attached the whole workbook file I am working on with data sample.

chartevents2.xls
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36901976
It's because of your chart and the way the selected event works.  Your chart has 3 separate series, each containing one point.  The first time you click and select, Excel is selecting all the points in the series, and the event finds that Arg2 is -1 and so does not display the msgbox.  -1 in Arg2 means that the whole series is selected.  The next time you click you select just the individual data point, then Arg2 is the number of the point and the msgbox fires. You can see this more clearly on the file I originally uploaded, which has several points in one series.

It ought to be simple to fix this, but it's not working properly at the moment - not sure if it is because of Excel 2003 - give me a few minutes.

Stuart
0
 
LVL 17

Accepted Solution

by:
andrewssd3 earned 1000 total points
ID: 36902027
Mmm - slightly strange one.  I was also getting the same problem as you, even when I changed the code to fire for a whole series as well - the very first click on the chart was working fine, but subsequently every alternate click did nothing.  However, I have added code to explicitly select the series or point and it now seems fine.  I was nervous of doing this because I thought doing the selection might cause the event to fire again and we would get an infinite loop, but it seems to be what's needed.
Private Sub objChart_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)

    Dim s As Excel.Series
    Dim p As Excel.Point
    
    If ElementID = xlSeries Then
    
        Set s = objChart.SeriesCollection(Arg1)
        Debug.Print Arg2
        If Arg2 > -1 Then
            Set p = s.Points(Arg2)
            p.Select
        Else
            Set p = s.Points(1)
            s.Select
            Arg2 = 1
        End If
        MsgBox s.Name & vbTab & s.XValues(Arg2) & vbTab & s.Values(Arg2)
        
    End If
    
End Sub

Open in new window

0
 

Author Closing Comment

by:bsaisu
ID: 36902417
Thank you for your quick complete answer. You are the man. For sure I will come back to you for more Help.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36904585
@andrewssd3 - I was looking at your solution and moved the embedded chart to a chart sheet.  As a result, no chart is visible and now none of my worksheets that have a chart tab actually display the chart.  Not sure its something in your code or something I've done.  Would you kindly try to move the embedded chart to a chart sheet and advise if you get the same unfortunate results?

Cheers,

Dave
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36904721
No - just tried this on Excel 2010, and the charts still show.  Obviously the events aren't still raised for the moved chart and I get an error when switching to the sheet that used to have it on.  I'd expect this as ChartObjects(1) is not longer there.

Maybe it is something specific to your worksheet - do you want to post it somewhere and I can see if it fails for me too?
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36906062
Thanks for the offer.  What I was trying to do was grab the data in your solution's example, and plop it into my solution, so that could be demonstrated as well.  My option (where you hit the INSERT key) can be preferable to the click event, especially at times when many datapoints are close together, so selecting the right DOT to get X,Y coordinates on can sometimes be a challenge.   one selects a point nearby, then uses the arrow keys to get the exact point desired. Also, the code is setup to run a macro on the X,Y coordinates, based on user selection of OK/CANCEL from the message that pops up.

Your solution is great, and I'm glad the OP is satisfied with the solution, but I just wanted to provide another alternative to those who search the knowledgebase, in case selecting a point becomes a challenge.

As a result, attached is my solution encapsulated as a chart tab, where selecting a datapoint, then hitting INSERT will prompt the X,Y coordinates and be in a position to do something with the X,Y point.  Note the use of ONKEY to catch the INSERT and the use of the EXCEL4 macro to catch what's currently selected.  Could this be done as well, via your methodology?

As to the "ERROR" I was having, I was able to correct via reboot, but was unable to replicate (it was related to having both our solutions open and moving code around).  I was able to get your to work with a chart sheet, by changing the _activate event statement to:

    Set mobjChartEvents.objChart = Chart1

Cheers,

Dave

chartEvents-r2.xlsm
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36906102
Dave

Yes - thanks.  I'm going to have a look a the INSERT solution, which I haven't seen before.  As you say hooking up an event handler class will work fine as long as you assign the right object to the class, and it is prone to stopping working, mostly when debugging, because various exceptions clear the allocation to the global variable and stop the events firing.

Cheers

Stuart
0
 

Author Comment

by:bsaisu
ID: 36954044
Hi Andrew,

I have a new open question. Would you have a look at it please?
0
 

Author Comment

by:bsaisu
ID: 36961506
andrewssd3,

I want to be able to click on the label as well in addition to the marker. How can i do it. could you edit the excel file you have sent me before?
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
I came across an unsolved Outlook issue and here is my solution.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question