How to get the index(number) of seriescollection selected in activechart ?

[imashish]

Is there any one to get the index number of a seriescollection within the activechart user has selected .
I am able access the chart selected by the user by using the activechart function, but next i am trying to access the seriescollection selected by the user, in other words the activeseriescollection thing.
Is there any way to access the active seriescollection of an activechart or any other way to access the seriescollecion of a chart selected by a user.
I googled a lot but what i found that we have give the index no. i.e; activechart.seriescollection(1)......
Any help is greatly appreciated.

Thanks in advance,
Ashish Pandey

 

[GopalaKrishnaJ]

We have to write a class event and a separate module in case you want to run the things at the time of selection

Run this module to assign chart to class

Public chartRegion             As New clsChart
Public objChart                As Chart
Sub ModAddChartToClass()
    Set chartRegion.chtClassChart = Sheet1.ChartObjects("Chart 1").Chart
End Sub

And now we need to create one class of same name "clsChart" and keep this code in it

Public WithEvents chtClassChart     As Chart
Dim lngElementID                          As Long
Dim lngX                                     As Long
Dim lngY                                     As Long

Private Sub chtClassChart_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
    If Button = 1 Then
        Call chtClassChart_Select(0, 0, 0)
    End If
End Sub
Private Sub chtClassChart_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)
    Set objChart = Nothing
    Set objChart = chtClassChart

    If ElementID = 21 Then
        objChart.SetElement (msoElementPrimaryCategoryAxisNone)
        objChart.SetElement (msoElementPrimaryValueAxisNone)
    Else
        If objChart.SeriesCollection.Count > 0 Then
            objChart.SetElement (msoElementPrimaryCategoryAxisShow)
            objChart.SetElement (msoElementPrimaryValueAxisShow)
        End If
    End If
    
    If Arg1 > 0 Then
        If chtClassChart.SeriesCollection.Count > 0 Then
            MsgBox chtClassChart.SeriesCollection(Arg1).Name
        End If
    End If
End Sub

 

[imashish]

We have to write a class event and a separate module in case you want to run the things at the time of selection

Run this module to assign chart to class

Public chartRegion             As New clsChart
Public objChart                As Chart
Sub ModAddChartToClass()
    Set chartRegion.chtClassChart = Sheet1.ChartObjects("Chart 1").Chart
End Sub

And now we need to create one class of same name "clsChart" and keep this code in it

Public WithEvents chtClassChart     As Chart
Dim lngElementID                          As Long
Dim lngX                                     As Long
Dim lngY                                     As Long

Private Sub chtClassChart_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
    If Button = 1 Then
        Call chtClassChart_Select(0, 0, 0)
    End If
End Sub
Private Sub chtClassChart_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)
    Set objChart = Nothing
    Set objChart = chtClassChart

    If ElementID = 21 Then
        objChart.SetElement (msoElementPrimaryCategoryAxisNone)
        objChart.SetElement (msoElementPrimaryValueAxisNone)
    Else
        If objChart.SeriesCollection.Count > 0 Then
            objChart.SetElement (msoElementPrimaryCategoryAxisShow)
            objChart.SetElement (msoElementPrimaryValueAxisShow)
        End If
    End If
    
    If Arg1 > 0 Then
        If chtClassChart.SeriesCollection.Count > 0 Then
            MsgBox chtClassChart.SeriesCollection(Arg1).Name
        End If
    End If
End Sub

Hey GopalaKrishnaJ,

Thanks for the prompt reply and i really appreciate your work.
Well after a long fight (search) i found a solution which actually suits my question. Actually i found something like "ExecuteExcel4Macro" which can be used to get the index of the selected chart series and its point as well. A piece of my work is below

Sub GetPointVal_Chart()

Dim ActSheet As Worksheet


Dim ActChrt As Chart


Dim SrPnt As Variant
Dim SrIndex As Integer
Dim PntIndex As Integer




Set ActSheet = ActiveSheet
If ActiveChart Is Nothing Then
MsgBox "Please select a chart, and retry"
Exit Sub
Else
Set ActChrt = ActiveChart
SrPnt = ExecuteExcel4Macro("Selection()")
SrIndex = Mid(SrPnt, 2, 1)
PntIndex = Right(SrPnt, 1)
End If

But i can use your piece of code in my work as whole.

Well a reference to the work by Jon Peltier which made my day is following.
http://www.pcreview.co.uk/forums/vba-stmt-tell-me-index-charts-series-formula-t1787265.html
Maybe u can have a look here.

Once again thanks a lot dude.

Thanks & Regards,
Ashish Pandey

 

[emranali1989]

Can the above code be done in chart in powerpoint.