XYChart interactive points/markers

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
bsaisuAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

andrewssd3Commented:
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
andrewssd3Commented:
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
dlmilleCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

bsaisuAuthor Commented:
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
andrewssd3Commented:
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
andrewssd3Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bsaisuAuthor Commented:
Thank you for your quick complete answer. You are the man. For sure I will come back to you for more Help.
0
dlmilleCommented:
@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
andrewssd3Commented:
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
dlmilleCommented:
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
andrewssd3Commented:
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
bsaisuAuthor Commented:
Hi Andrew,

I have a new open question. Would you have a look at it please?
0
bsaisuAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.