Thread: How to get graph data using Chart Events?

I have a Volume vs. Area plot. Here is the format of the worksheet

length | width | depth | Area | Volume

I would like to use chart events (Mouse Up event) to get me information about not only area and volume but also l, w, and d.

I know that following code give me info about x and y (i.e. area and volume.) But how to modify this code, so that I also get
"L= , W= , D= , A= , V= ."

Thanks in advance.

Code:

 With ActiveChart
        ' Pass x & y, return ElementID and Args
        .GetChartElement x, y, ElementID, Arg1, Arg2
 
        ' Did we click over a point or data label?
        If ElementID = xlSeries Or ElementID = xlDataLabel Then
            If Arg2 > 0 Then
                ' Extract x value from array of x values
                myX = WorksheetFunction.Index _
                    (.SeriesCollection(Arg1).XValues, Arg2)
                ' Extract y value from array of y values
                myY = WorksheetFunction.Index _
                    (.SeriesCollection(Arg1).Values, Arg2)
 
                ' Display message box with point information
                MsgBox "Series " & Arg1 & vbCrLf _
                    & """" & .SeriesCollection(Arg1).Name & """" & vbCrLf _
                    & "Point " & Arg2 & vbCrLf _
                    & "X = " & myX & vbCrLf _
                    & "Y = " & myY
            End If
        End If
    End With

hi,

People often find it easier to help if they have a starting point to work from. Can you please attach a small example file (using the paperclip icon or the "manage attachments" button) showing your layout etc?

Rob

You would need to use the Arg1 and Arg2 values locate a cell within a known range.

See attached.

It assumes you know where the data table is located.

Code:

Private Sub Chart_MouseUp(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)

    Dim lngArg1 As Long
    Dim lngArg2 As Long
    Dim lngElementID As Long
    Dim rngData As Range
    Dim myX, myY
    
    Set rngData = Worksheets("Sheet1").Range("A2:E9")
    With ActiveChart
        ' Pass x & y, return ElementID and Args
        .GetChartElement x, y, lngElementID, lngArg1, lngArg2
        
        ' Did we click over a point or data label?
        If lngElementID = xlSeries Or lngElementID = xlDataLabel Then
            If lngArg2 > 0 Then
                ' Extract x value from array of x values
                myX = WorksheetFunction.Index _
                            (.SeriesCollection(lngArg1).XValues, lngArg2)
                ' Extract y value from array of y values
                myY = WorksheetFunction.Index _
                            (.SeriesCollection(lngArg1).Values, lngArg2)
                
                ' Display message box with point information
                MsgBox "Series " & lngArg1 & vbCrLf _
                & """" & .SeriesCollection(lngArg1).Name & """" & vbCrLf _
                & "Point " & lngArg2 & vbCrLf _
                & "X = " & myX & vbCrLf _
                & "Y = " & myY
            
                MsgBox "Length=" & rngData.Cells(lngArg2, 1) & vbLf & _
                       "Width=" & rngData.Cells(lngArg2, 2) & vbLf & _
                       "Depth=" & rngData.Cells(lngArg2, 3)

            End If
        End If
    End With
    
End Sub