CODEWITHSUNDEEP
vbaexcelcharts
Mikku
Mikku

Reputation: 6654

Chart Event - Values not being picked up by code for more than 1 series of data

I trying to build an interactive chart to represent set of data. In the below screenshot if we select different buttons we can select different data series for the chart. My problem is that by using the below code If series 1 is true only then I am able to get the values for myX and myY ...for other series selected I only get blanks.

enter image description here

Private Sub Chart_MouseUp(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

        ' Did we click over a point or data label?
        If ElementID = xlSeries Or ElementID = xlDataLabel Then

        On Error Resume Next

            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)


          MsgBox myX & ", " & myY

Basically the line 

 myX = WorksheetFunction.Index(.SeriesCollection(Arg1).XValues, Arg2)

throws an error Parameter not Valid if series 2 or 3 is selected. Works fine if series 1 is selected.

myX gives me the name of the clicked country and myY the value.

Video Link to see what's happening in this excel file.

Chart Template Video Youtube

Upvotes: 2

Views: 653

Answers (1)

Mikku
Mikku

Reputation: 6654

Silly but if we use FullSeriesCollection instead of SeriesCollection it works.

So instead of line 

myX = WorksheetFunction.Index(.SeriesCollection(Arg1).XValues, Arg2)

I have to use:

myX = WorksheetFunction.Index(.FullSeriesCollection(Arg1).XValues, Arg2)

The explanation seems to be given by the documentation for FullSeriesCollection:

The FullSeriesCollection Object (Excel) object enables you to get a filtered out Series Object (Excel) object and filter it back in. It also enables you to iterate over the full set of Series object, filtered out or visible, programmatically. By having the existing SeriesCollection Object (Excel) object contain only the visible series you can programmatically perform operations on only the visible series. It also prevents Microsoft Excel from breaking existing chart solutions on charts with filtered out data.

Upvotes: 2

Related Questions