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

imashish

New Member
Joined
Apr 23, 2012
Messages
11
Is there any one to get the index number of a seriescollection within the activechart user has selected :confused:.
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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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

Code:
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

Code:
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
 
Upvote 0
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

Code:
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

Code:
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top