XYChart interactive points/markers

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

Select allOpen 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

Select allOpen 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

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

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

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

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

Thank you for your quick complete answer. You are the man. For sure I will come back to you for more Help.

@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

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?

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

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

Hi Andrew,

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

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?