CODEWITHSUNDEEP
vbaexcel
Jordan
Jordan

Reputation: 697

Selecting and Retrieving data point - Embedded Charts -VBA

I have a table(Table1) which I have used to create a Scatter diagram("Chart Title") in the same sheet - Embedded chart. I want to select a data point and retrieve is values. I found the following online. 

Dim myClassModule() As New EventClassModule

Sub InitializeChart()
    If ActiveSheet.ChartObjects.Count > 0 Then
    ReDim myClassModule(1 To ActiveSheet.ChartObjects.Count)

    Dim chtObj As ChartObject
    Dim chtnum As Integer

    For Each chtObj In ActiveSheet.ChartObjects
        chtnum = chtnum + 1
        Set myClassModule(chtnum).myChartClass = chtObj.Chart
    Next
  End If
 End Sub

 Sub ResetCharts()
 Dim chtnum As Integer

 For chtnum = 1 To UBound(myClassModule)
    Set myClassModule(chtnum).myChartClass = Nothing
 Next
 End Sub

This goes in the class module

  Public WithEvents myChartClass As Chart

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

  Dim ElementID As Long, Arg1 As Long, Arg2 As Long
  Dim myX As Variant, myY As Double

  With ActiveChart
   .GetChartElement x, y, ElementID, Arg1, Arg2

  If ElementID = xlSeries Or ElementID = xlDataLabel Then
   If Arg2 > 0 Then
      myX = WorksheetFunction.Index _
   (.SeriesCollection(Arg1).XValues, Arg2)

   myY = WorksheetFunction.Index _
   (.SeriesCollection(Arg1).Values, Arg2)
   MsgBox (Arg1 & Chr(10) & Arg2)
   End If
  End If
 End With
 End Sub

But the following line:

Dim myClassModule() As New EventClassModule

is causing an error:

Used - defined type not defined & Cannot perform requested operation

Any ideas why?

Upvotes: 1

Views: 1471

Answers (1)

Patrick Lepelletier
Patrick Lepelletier

Reputation: 1652

using your code , i got this:

in the normal module (any name):

Option Explicit

Dim myClassModule() As New EventClassModule

Sub InitializeChart()
    If ActiveSheet.ChartObjects.Count > 0 Then
        ReDim myClassModule(1 To ActiveSheet.ChartObjects.Count)

        Dim chtObj As ChartObject
        Dim chtnum As Long 'Integer

        For Each chtObj In ActiveSheet.ChartObjects
            chtnum = chtnum + 1
            Set myClassModule(chtnum).myChartClass = chtObj.Chart
        Next
    End If
End Sub

 Sub ResetCharts()
 'Dim chtnum As Long 'Integer

 If Not myClassModule Is Nothing Then
 '   For chtnum = 1 To ubound(myClassModule)
 '       Set myClassModule(chtnum).myChartClass = Nothing
 '   Next  
 Erase myClassModule
 End If

 End Sub



Sub entry()
Dim i&
Dim j&
For i = 1 To 10
    For j = 1 To 10
        Cells(i, j) = 100 * Rnd
    Next j
Next i

ActiveSheet.Shapes.AddChart2(286, xl3DColumn).Select
ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$J$10")

End Sub

and in a class module (named EventClassModule):

Option Explicit


Public WithEvents myChartClass As Chart



Private Sub Class_Terminate()
Set myChartClass = Nothing
End Sub



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

Dim ElementID As Long, Arg1 As Long, Arg2 As Long
Dim myX As Variant, myY As Double

With myChartClass 'ActiveChart
    .GetChartElement x, y, ElementID, Arg1, Arg2

    If ElementID = xlSeries Or ElementID = xlDataLabel Then
        If Arg2 > 0 Then
            myX = WorksheetFunction.Index(.SeriesCollection(Arg1).XValues, Arg2)

            myY = WorksheetFunction.Index(.SeriesCollection(Arg1).Values, Arg2)
            MsgBox (Arg1 & Chr(10) & Arg2)
        End If
    End If

End With

End Sub

Upvotes: 1

Related Questions