ActiveChart Mouse Click Graph

**bbene** · 03-24-2009, 12:47 PM

I have a long Macro that runs and within the macro a graph is created (in the same sheet as the data) so now I have an "ActiveChart" that is an XY scatter scatter smooth no markers graph. I want to be able to click three (3) times on this graph and have those points (their x and y value) to be assigned to a particular cell, (we'll say we should put the X value of the first point that was clicked in "AA1" and its Y value in "AB1", then the second point would be in "AA2" and "AB2", followed by the third point going to "AA3" and "AB3". I have no idea how to even start this, but I just have an active chart that is made in the middle of the macro and I want to be able to click on that chart three times, record the data values, then have my macro finish running. Is there a way to do this!? Thanks!

**Andy Pope** · 03-24-2009, 12:53 PM

    With ActiveChart
        With .SeriesCollection.NewSeries
            .XValues = ActiveSheet.Range("AA1:AA3")
            .Values = ActiveSheet.Range("AB1:AB3")
        End With
    End With

**bbene** · 03-24-2009, 01:05 PM

Thanks for the reply, but it doesn't seem to quite answer the question.

I have a macro that creates a graph. Then, using this active graph, I want to have a user instructed to click three points on the active graph. As the user clicks on each point I want the data values of each point to be put into cells "AA1:AA3" and "AB1:AB3". So, when one point is clicked that points, X Y value will go into cell AA1 and AB1 respectively. Is this possible?

**Andy Pope** · 03-25-2009, 05:43 AM

In that case you will need to capture the click events of the chart.

Can you post example of your chart

**bbene** · 03-25-2009, 12:13 PM

Here is quick blurb that should suffice to explain what I'm trying to do:

 Sub Graph_and_click()
    Name = "'" & ActiveSheet.Name
    Charts.Add
    ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).XValues = "=" & Name & "!R3C1:R5003C1"
    ActiveChart.SeriesCollection(1).Values = "=" & Name & "!R3C2:R5003C2"
    ActiveChart.Location Where:=xlLocationAsObject, Name:=Name
        
    'Now I want to be able to click three time on the active chart and have the X and Y values of the points I click be put into any D5:D8 E5:E8, respectively
    
 End Sub

See the attached file for details on the data. Thanks!

**Andy Pope** · 03-25-2009, 12:46 PM

Standard code module

 Sub Graph_and_click()
    Name = ActiveSheet.Name
    
    Range("D5:E8").Clear
    Range("A3").Select
    
    Charts.Add
    ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).XValues = "='" & Name & "'!R3C1:R5003C1"
    ActiveChart.SeriesCollection(1).Values = "='" & Name & "'!R3C2:R5003C2"
    ActiveChart.Location Where:=xlLocationAsObject, Name:=Name
        
    'Now I want to be able to click three time on the active chart and have the X and Y values of the points I click be put into any D5:D8 E5:E8, respectively
    
    Set m_clsChtEvt = New Class1
    Set m_clsChtEvt.ChartEvent = ActiveChart
    Set m_clsChtEvt.DataTable = Range("D5:E8")
    
 End Sub

Class module called Class1

Private m_rngDataTable As Range
Private WithEvents m_ChtEvt As Chart
Public Property Set ChartEvent(RHS As Chart)
    Set m_ChtEvt = RHS
End Property


Public Property Set DataTable(RHS As Range)
    Set m_rngDataTable = RHS
End Property

Private Sub m_UpdateTable(XValue As Single, YValue As Single)

    Dim lngRow As Long
    
    With m_rngDataTable
        For lngRow = 1 To .Rows.Count
            If Len(.Cells(lngRow, 1)) = 0 Then
                .Cells(lngRow, 1) = XValue
                .Cells(lngRow, 2) = YValue
                Exit Sub
            End If
        Next
    End With
    
    MsgBox "Exceed number of recorded clicks", vbExclamation
    
End Sub

Private Sub m_ChtEvt_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)

    Dim sngX As Single
    Dim sngY As Single
    Dim objSeries As Series
    
    If ElementID = xlSeries Then
        If Arg2 > 0 Then
            Set objSeries = m_ChtEvt.SeriesCollection(Arg1)
            sngX = Application.WorksheetFunction.Index(objSeries.XValues, Arg2)
            sngY = Application.WorksheetFunction.Index(objSeries.Values, Arg2)
        
            m_UpdateTable sngX, sngY
        End If
    End If
    
End Sub

The user needs to select the series and then select a point.

**bbene** · 03-25-2009, 01:07 PM

I'm a bit confused on how this is supposed to work. I tried running the macro and the graph was made then I clicked on the series (as you prescribed) then clicked on a point and was immediately hit with a "type mismatch" error. I'm not sure why or how to fix it but it may be because I did something wrong. Also, should the code you wrote put the points that the user clicks in any particular cell, say D5 and E5 then D6, E6 and so on? Again, I apologize if this is a mistake on my part.

**Andy Pope** · 03-26-2009, 04:49 AM

Try this with the example I posted.

Delete any existing charts on the sheet.
Clear the contents of the range D5:E8

Now run the macro. The xy values will be stored in D5:E8. Once this range is filled a message will be displayed.
Also not that the code will not run when the workbook is opened again, even if the cells are clear. For that you would need to create an instance of the chart event object and assign the references to the chart and cells.