Solved: Extract info of a point on a chart

[Digita]

Hi guys,

I have a line chart containing a series of 12 monthly periods. I can loop through the elements under the chart collection to obtain all values of the series.

As we hover the mouse over the line graph, a small box pops up showing the values of the x & y axis of any point. Using VBA, how can we quickly extract the x & y values pertaining to a single point? Something similar to extracting the contents of a comment box from a current cell perhaps?

Thanks in advance for your response.

Regards


kp

[DoLoop]

You can access the info with ActiveChart.GetChartElement. In your case your code could look like this (place it in the Chart section of the VBE)

[vba]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
Dim xlSeries As Series
Dim xlPoint As Point
Dim arrX() As Variant
Dim arrY() As Variant
Dim valueX As Long
Dim valueY As Long

'If Shift+LMB is pressed then
If Button = 1 And Shift = 1 Then
'For x, y, IDNum, a & b -> see VBA reference
ActiveChart.GetChartElement x, y, IDNum, a, b
If IDNum = 3 Then '( = xlSeries)
Set xlSeries = ActiveChart.SeriesCollection(a)
Set xlPoint = xlSeries.Points(b)
arrX = xlSeries.XValues
arrY = xlSeries.Values
valueX = CLng(arrX(b))
valueY = CLng(arrY(b))
MsgBox "X value = " & valueX & vbCrLf & "Y value = " & valueY
End If
End If

End Sub
[/vba]

I've put an example in attachment

[Digita]

Brilliant. Thank you so much DoLoop. Your code certainly helps me a lot.

Best regards


kp