Get the number of the active series in an active chart

[shark2000]

I am trying to make a fairly easy macro to activate the next series in an already active chart. (my main macro contains all the formatting stuff).
Problem with this coding, is that i am not able to assign the current active series (already selected in the graph in excel). In a graph with n series (SeriesCollection.Count = n) I want to go from x, to x+1 and if x=n i want to go back to series 1 and then just stop (so the for/next included here is not necessary if I get the macro to work as intended).

Sub NextButton_Click()

Dim cht As Chart
Set cht = ActiveChart
 
   If cht Is Nothing Then
   MsgBox "Select a chart."
   Exit Sub
   End If
      
       With cht
      
           For SrsIndx = 1 To .SeriesCollection.Count
           .SeriesCollection(SrsIndx).Select
           Next SrsIndx
          
       End With

End Sub

 

[Jon Peltier]

Selection.PlotOrder returns the index of the selected series.

 

[shark2000]

Thanks Jon - However, It has to work on combo charts as well - this was resolved by adding the following function

Function ActiveSeriesNumber(ThisSeries AS Series) AS Long
    Dim ThisChart AS Chart, Ser AS Long
    ActiveSeriesNumber = -1
    On Error GoTo FunctionError

    Set ThisChart = ThisSeries.Parent.Parent 'Object Model Is Chart.ChartGroup.Series
    For TestNumber = 1 to ThisChart.SeriesCollection.Count
        If ThisChart.SeriesCollection(Ser).Name = ThisSeries.Name Then
            ActiveSeriesNumber = Ser
            Exit Function
        End If
    Next Ser

FunctionError:
    On Error GoTo -1
End Function

 

[Jon Peltier]

Be careful with that. It is possible to have more than one series in a chart with the same name.

Try this. I extract the series number from the series formula, then add 1 (or reset to 1 if it's the last series), and select the next series:

Sub SelectNextSeries()
  If TypeName(Selection) = "Series" Then
    Dim sFormula As String
    sFormula = Selection.Formula
    Dim vFormula As Variant
    vFormula = Split(sFormula, ",")
    Dim iSeriesNumber As Long
    iSeriesNumber = Val(vFormula(UBound(vFormula)))
    If iSeriesNumber = ActiveChart.SeriesCollection.Count Then
      iSeriesNumber = 1
    Else
      iSeriesNumber = iSeriesNumber + 1
    End If
    ActiveChart.SeriesCollection(iSeriesNumber).Select
  End If
End Sub

 

[shark2000]

Good point - thanks. Not sure why this is so complicated, but is there a similar solution for selecting next data point within a series (e.g. from already-active point x to x+1 in a pie-chart)?

 

[Jon Peltier]

Sub SelectNextPoint()
  If TypeName(Selection) = "Point" Then
    Dim sName As String
    sName = Selection.Name ' "S1P1"
    sName = Mid$(sName, InStr(sName, "S") + 1)
    Dim vName As Variant
    vName = Split(sName, "P")
    Dim iSrs As Long
    iSrs = Val(vName(LBound(vName)))
    Dim iPoint As Long
    iPoint = Val(vName(UBound(vName)))
    If iPoint = ActiveChart.SeriesCollection(iSrs).Points.Count Then
      iPoint = 1
    Else
     iPoint = iPoint + 1
    End If
    ActiveChart.SeriesCollection(iSrs).Points(iPoint).Select
  End If
End Sub