Retrieving user-clicked chart point values in Excel 2

[N1GHTEYES]

I'm trying to write some code which will let me edit some data based on user-selection by means of double-clicking on the relevant point in an xy chart.

Eventually I need to be able to figure out how to set it up so that if the user double-clicks on a chart point, I can identify in code which cells in the workbook that datapoint came from.

However, I'm just working up the process at the moment and I wanted to check i can at least correctly identify such a point. So I created a chart (on it's own sheet so I have access to chart events without the hassle of withevents etc) and put the following code in the BeforeDouble_Click event of the chart:

Private Sub Chart_BeforeDoubleClick(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean)
Dim xval As Long, yval As Long
If ElementID = xlSeries Then
  xval = ActiveChart.SeriesCollection(Arg1).XValues(Arg2)
  yval = ActiveChart.SeriesCollection(Arg1).Values(Arg2)
  MsgBox "X = " & Str(xval) & Chr(13) & "Y = " & Str(yval)
End If
End Sub

As far as I can see this should issue a message showing the x and y values of the point the user double-clicked, but it doesn't. I throws an error on the line:

xval = ActiveChart.SeriesCollection(Arg1).XValues(Arg2)

If I stop the code at this point and examine the values of arg1 and arg2, they correctly show the index numbers of the series and the point I clicked on. From the help, it seems to me that the above code should work. If I put a watch on the seriescollection, it shows that xvalues() and values() are both valid arrays and I can see the values in them, but when I try to select one of those values using arg2, it crashes. What am I doing wrong?

Tony

 

[SkipVought]

hi,

Private Sub Chart_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
    Dim IDNum As Long
    Dim a As Long
    Dim b As Long
    
    ActiveChart.GetChartElement x, y, IDNum, a, b
    
    Select Case IDNum
        Case xlSeries
            Application.StatusBar = "Series is " & a & " Data value is " & b & " X: " & x & " Y: " & y
    End Select
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

A correction for the Data Point and the addition of the Data Value AND using Named Ranges...

Private Sub Chart_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
    Dim IDNum As Long
    Dim a As Long
    Dim b As Long
    
    ActiveChart.GetChartElement x, y, IDNum, a, b
    
    Select Case IDNum
        Case xlSeries
            Application.StatusBar = "Series is " & a & " Data point is " & b & " Data VALUE is " & Sheets("ChartData").Range("Amt")(b)
    End Select
End Sub

where my chart source data is
[tt]
name amt
a 1
s 3
d 8
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[N1GHTEYES]

Hi Skip,

Thanks for the reply, but I'm a bit confused.

I think I might not have made myself clear.

I am using an actual xy chart - i.e. what MS calls a "scatter" plot. So there will be a range of cells holding x data values, and another range holding y values. I don't know what you mean by

where my chart source data is
name amt
a 1
s 3
d 8

Are you assuming a sheet holding a named range called "amt"?
What do your a, s and d refer to?

To take a simplistic example, I would expect to have, say,

x = sheet1!A1:A3 = 2.1, 3.5, 7,4
y = sheet1!B1:B3 = 6.2, 4.8, 2.5

I would plot y against x. I was trying to write some code such that, when the user clicks on point 2, say, it returns:

x value = 3.5
y value = 4.8

Though ultimately I also need to be able to also return

x location = sheet1!A2
y location = sheet1!B2

At design time, I will not know the location of the ranges fom which the data comes (that will be determined by the nature of each datset loaded). With the current design of the workbook, I will know the column from which the x and y datasets come, but I won't know the rows at which each datset starts and ends. Each datset will have its own series on the chart. So which series the user clicks will tell me which datset it is, but I ultimately need to know which row the data comes from.

If it helps, the main reason for doing this is that almost all of the raw data files will necessarily contain a small number of erroneous points. Analysing the data with these points included would severely skew the results, but identifying them automatically would be extremely difficult. However the user could do so by eye very easily, so when he loads a data file, the workbook partitions the data into its appropriate datasets and plots them as individual series. On the chart, the erroneous points will be glaringly obvious. He then clicks on these points, and the code will then identify which rows they come from and delete those rows before finally processing the data to obtain the required information.

Is that clear? I hope so, but it is after 2:00am here at the moment and my lucidity is probably starting to fade...

Tony

p.s. did you ever think of a suitable punishment for the jerk?

 

[SkipVought]

OK

my data
[tt]
x y
2 1
4 3
7 8
[/tt]

Private Sub Chart_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
    Dim IDNum As Long
    Dim a As Long
    Dim b As Long
    
    ActiveChart.GetChartElement x, y, IDNum, a, b
    
    Select Case IDNum
        Case xlSeries
            Application.StatusBar = "Series is " & a & " Data point is " & b & " Data VALUE is " & Sheets("ChartData").Range("x")(b) & "," & Sheets("ChartData").Range("y")(b)
    End Select
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

Tony,
in reference to your initial question ("What am I doing wrong?"):

XValues can't be initially treated as array, however it returns variant. One can use intermediary variable:

Private Sub Chart_BeforeDoubleClick(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean)
Dim xSeries As Variant, ySeries As Variant
If ElementID = xlSeries And Arg2 <> -1 Then
    Cancel = True
    xSeries = ActiveChart.SeriesCollection(Arg1).XValues
    ySeries = ActiveChart.SeriesCollection(Arg1).Values
    MsgBox "X = " & CStr(xSeries(Arg2)) & Chr(13) & "Y = " & CStr(ySeries(Arg2))
End If
End Sub

combo

 

[N1GHTEYES]

Skip, combo - Thanks a lot. Both of the above work a treat to find the actual x and y data values.

combo - I'd almost got there myself, but not quite. I thought the problem might be that the xvalues and values properties might have been ranges, so I'd tried assigning them to variants already, but I'd used "set xval = ...". That had failed, but your method (treating them as arrays contained in variants) works fine. I still don't quite understand why they don't act like other array properties (i.e. where you can address them directly), but hey, what works, works. I'm particularly grateful for your helping me understand how to use the before double_click event because this will eventually lead to data being deleted, so I'd prefer something more "positive" than a single click.

Now my only remaining problem is to get the source locations (i.e. sheet, row, column) from the user-click. I will not know at the time of writing the code which ranges on the worksheet are referred to by the chart series'. So I need to be able to get that info from the chart itself.

If I right-click a chart series, it gives me the drop down option to see what range it refers to - so I know the chart "knows" the answer, but I can't find how to get at it. I've checked the chart properties & methods in the Object Browser, and found the "setsourcedata" method but not a "getsourcedata" method or even a "sourcedata" property.

I could, if push comes to shove, make the workbook keep a table of which chart series come from which range addresses, then use the series and point indices from the user-click to find the source cells of the clicked point, but I'd rather simply extract the info from the chart itself if possible. So, is it possible?

Tony

 

[SkipVought]

The reason I've been ingnoring part of your original request is that I remembered reading something several years ago. I have found it, from John Walkenbach.

http://spreadsheetpage.com/index.php/tip/a_class_module_to_manipulate_a_chart_series/

Excel's object model has a serious flaw: There is no direct way to to use VBA to determine the ranges used in a chart. This tip contains a useful class module that can simplify the task of manipulating chart's using VBA.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[N1GHTEYES]

Thanks Skip, have a star to add to your collection (you too combo - sorry I forgot earlier). Where would we mere mortals be without you?

That is frustrating. Obviously the chart has an internal reference to the source range (it is highlighted when you click on the series, so it MUST have), but they've decided not to share the info with us. Ho hum.

I'm not sure at the moment whether I will go down the Walkenbach route, or follow the other suggestion I made earlier (have the workbook keep a list of which ranges it has assigned to which charts / series then use the user-clicked series and point index values to find the cell within the appropriate range).

If I come up with anything worth sharing I'll post it.

Thanks again for your help.

Tony

 

[SkipVought]

you could build a cross ref table to relate the series number (a) to the range and you already have the range point index (b).

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

Thanks. The Series object has the family of Formula properties, not nice but the string can be analysed.

combo

 

[N1GHTEYES]

Skip - yes, I think what you are suggesting is what I meant by making the workbook keep a record of which ranges it assigned to which charts / series.

combo - yes, I spotted that in the Walkenbach page Skip posted. That might be the best method, but I wasn't sure if it was a readable property. It looked like it might be how you could assign a range, but not necessarily read it. Unfortunately, before I could investigate, my blasted boss dropped yet another "urgent" distraction on me. I'll have a play as soon as I can.

Tony

 

[N1GHTEYES]

OK, so here is my attempt to cobble together the ideas from Skip & combo into some code which will delete the row containing the data behind a point which the user has double-clicked.

Private Sub Chart_BeforeDoubleClick(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean)
Dim frmla As String, addressx As String
Dim rngx As Range
Dim frmlarray As Variant
If ElementID = xlSeries Then
    frmla = ActiveChart.SeriesCollection(Arg1).Formula
    addressx = Split(Mid(frmla, InStr(frmla, "(") + 1), ",")(1)
    Set rngx = Range(addressx)
    Worksheets(rngx.Worksheet.Name).Rows((rngx.Row) + Arg2 - 1).Delete
    Cancel = True
End If
End Sub

Note - this assumes that
1 the x and y ranges are both in columns
2 the x and y range value pairs are both on the same rows (i.e. 3 the x and y ranges both start on the same row)
4 the x and y ranges are each contiguous
5 the chart in question is an xy (scatter) plot
6 the user first selects the series THEN double_clicks on the point he wants to delete.

There is no error checking in there and I'd hardly call it elegant, but it works.

Now all I've got to do is get my head around working with embedded charts, withevents etc...

Thanks again guys.

Tony

 

[SkipVought]

sheet embedded charts do not come out-of-the-box with events.

You will need to add a class module and appropriate code to expose these events.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[N1GHTEYES]

Thanks Skip - I've been glancing at related posts, so I already have some idea what is involved (hence my reference to withevents etc), but I have yet to knuckle down and properly try to understand it and put it into practice.

Tony