Referencing a series name that contains data linked to a cell VBA

[hannnahheileen]

Hello all, I am trying to format series' line color on a graph. However, when I try to reference the names of the series to format the line color I am getting stuck because the names contain a reference to a cell value on that specific sheet. Each SN (serial number) is on a separate sheet in the workbook. Here is a snippet of my code.

   'Loop through all worksheets in the active workbook
    For Each currentWorksheet In Worksheets
        'Add a ESR series
        Set currentSeries = ZESR.SeriesCollection.NewSeries
        'Name and assign XY values to the series from the current worksheet
        With currentSeries
            .Name = currentWorksheet.Name & " ESR, " & currentWorksheet.Range("P5") & " mOhms @ 100 kHz"   'SERIES NAME I AM REFERRING TO
            .XValues = "='" & currentWorksheet.Name & "'!$A$10:$A$405"
            .Values = "='" & currentWorksheet.Name & "'!$D$10:$D$405"
            
        End With
    Next currentWorksheet

Application.ScreenUpdating = True


'FORMAT AXES ELEMENTS
    With ZESR
        .Axes(xlCategory).ScaleType = xlLogarithmic
        .Axes(xlValue).ScaleType = xlLogarithmic
        .Axes(xlCategory).MinimumScale = 100
        .Axes(xlCategory).MaximumScale = 100000000
        .Axes(xlCategory).HasMajorGridlines = True
        .Axes(xlCategory).MajorGridlines.Border.Color = RGB(0, 0, 0)
        .Axes(xlCategory).HasMinorGridlines = True
        .Axes(xlCategory).MinorGridlines.Border.Color = RGB(0, 0, 0)
        .Axes(xlValue).HasMajorGridlines = True
        .Axes(xlValue).MajorGridlines.Border.Color = RGB(0, 0, 0)
        .Axes(xlValue).HasMinorGridlines = True
        .Axes(xlValue).MinorGridlines.Border.Color = RGB(0, 0, 0)
        .SetElement (msoElementLegendRight)
        .Location Where:=xlLocationAsNewSheet, Name:="Impedance & ESR"
        .Axes(xlCategory).TickLabels.NumberFormat = "#,##0.00"
        .Axes(xlCategory).MaximumScale = 100000000
        .Axes(xlCategory).MinimumScale = 100
        .Axes(xlValue).TickLabels.NumberFormat = "#,##0.00"
        .Axes(xlValue).MinimumScale = 0.01
    End With

'Y-AXIS
With ZESR
    With .Axes(xlValue, xlPrimary)
            .TickLabelPosition = xlLow
            .TickLabels.NumberFormat = "General"
            .HasTitle = True
         With .AxisTitle
            .Caption = "Impedance & ESR"
            .Font.Bold = True
            .Font.Size = 10
            .Font.Name = "Arial"
            .Font.Color = RGB(0, 0, 0)
         End With
            With .TickLabels
            .Font.Size = 8
            .Font.Name = "Arial"
        End With
    End With
End With

'X-AXIS
With ZESR
    With .Axes(xlCategory, xlPrimary)
        .TickLabelPosition = xlLow
        .TickLabels.NumberFormat = "General"
        .HasTitle = True
        With .AxisTitle
            .Caption = "f (Hz)"
            .Font.Bold = True
            .Font.Size = 10
            .Font.Name = "Arial"
            .Font.Color = RGB(0, 0, 0)
        End With
          With .TickLabels
            .Font.Size = 8
            .Font.Name = "Arial"
        End With
    End With
End With

'LEGEND
    With ZESR.Legend
        .Position = xlLegendPositionTop
        .Width = 150
        .Height = 120
        .Font.Size = 6
        .Font.Name = "Arial"
        .Font.Color = RGB(0, 0, 0)
        .Border.Weight = xlHairline
        .Border.Color = RGB(0, 0, 0)
        .Interior.Color = RGB(255, 255, 255)
        End With

'TITLE
    With ZESR
        .HasTitle = True
        .ChartTitle.Select
        .ChartTitle.Text = "Impedance and ESR vs. Frequency" & vbLf & "PART#  ESPEC  BATCH#  REQUEST#" & vbLf & "FREQUENCY SCAN"
        .ChartTitle.Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(0, 0, 0)
        .ChartTitle.Font.Size = 10
        .ChartTitle.Font.Bold = True
        .ChartTitle.Font.Name = "Arial"
    End With

ActiveChart.SetElement (msoElementChartTitleCenteredOverlay)


'TEXTBOX

Set cht = Charts("ZESR")

Set txtB = ZESR.TextBoxes.Add(10, 465, 200, 40)
With txtB
    .Name = "MyTextbox"
    .Border.Line.Visible = msoFalse
    .Text = "ESR - Solid Line" & vbLf & "Impedance - Dashed Line"
    .Font.Name = "Arial"
    .Font.Size = 8
    .Font.Bold = True
End With

'RE-ARRANGE LEGEND
        'Assigning Series names to an array
        For i = LBound(Arr) To UBound(Arr)
        Arr(i) = ActiveChart.FullSeriesCollection(i).Name
        Next i

        'Bubble-Sort (Sort the array in increasing order)
        For r1 = LBound(Arr) To UBound(Arr)
            rval = Arr(r1)
                For r2 = LBound(Arr) To UBound(Arr)
                    If Arr(r2) > rval Then 'Change ">" to "<" to make it decreasing
                        Arr(r1) = Arr(r2)
                        Arr(r2) = rval
                        rval = Arr(r1)
                    End If
                Next r2
        Next r1

    'Defining the PlotOrder
    For i = LBound(Arr) To UBound(Arr)
    ActiveChart.FullSeriesCollection(Arr(i)).PlotOrder = i
    Next i


'FORMATTING LINES
    Application.ScreenUpdating = False

     With ActiveChart
        For Each srs In .SeriesCollection
        Select Case srs.Name
            Case "SN 1 Impedance"
                srs.Format.Line.ForeColor.RGB = RGB(128, 0, 0)
                srs.Format.Line.DashStyle = msoLineLongDash
            Case "SN 2 Impedance"
                srs.Format.Line.ForeColor.RGB = RGB(255, 0, 0)
                srs.Format.Line.DashStyle = msoLineLongDash
            Case "SN 3 Impedance"
                srs.Format.Line.ForeColor.RGB = RGB(255, 128, 0)
                srs.Format.Line.DashStyle = msoLineLongDash
            Case "SN 4 Impedance"
                srs.Format.Line.ForeColor.RGB = RGB(0, 128, 255)
                srs.Format.Line.DashStyle = msoLineLongDash
            Case "SN 5 Impedance"
                srs.Format.Line.ForeColor.RGB = RGB(0, 128, 0)
                srs.Format.Line.DashStyle = msoLineLongDash
            Case "SN 6 Impedance"
                srs.Format.Line.ForeColor.RGB = RGB(45, 75, 173)
                srs.Format.Line.DashStyle = msoLineLongDash
                
            Case "SN 1 ESR, " & currentWorksheet.Range("P5") & " mOhms @ 100 kHz"     'REFERENCE I AM USING NOT FORMATTING LINE COLOR
                srs.Format.Line.ForeColor.RGB = RGB(128, 0, 0)
            Case "SN 2 ESR, " & currentWorksheet.Range("P5") & " mOhms @ 100 kHz"     'REFERENCE I AM USING NOT FORMATTING LINE COLOR
                srs.Format.Line.ForeColor.RGB = RGB(255, 0, 0)
            Case "SN 3 ESR, " & currentWorksheet.Range("P5") & " mOhms @ 100 kHz"     'REFERENCE I AM USING NOT FORMATTING LINE COLOR
                srs.Format.Line.ForeColor.RGB = RGB(255, 128, 0)
            Case "SN 4 ESR, " & currentWorksheet.Range("P5") & " mOhms @ 100 kHz"     'REFERENCE I AM USING NOT FORMATTING LINE COLOR
                srs.Format.Line.ForeColor.RGB = RGB(0, 128, 255)
            Case "SN 5 ESR, " & currentWorksheet.Range("P5") & " mOhms @ 100 kHz"     'REFERENCE I AM USING NOT FORMATTING LINE COLOR
                srs.Format.Line.ForeColor.RGB = RGB(0, 128, 0)
            Case "SN 6 ESR, " & currentWorksheet.Range("P5") & " mOhms @ 100 kHz"     'REFERENCE I AM USING NOT FORMATTING LINE COLOR
                srs.Format.Line.ForeColor.RGB = RGB(45, 75, 173)
            End Select
        Next
    End With

Application.ScreenUpdating = True

 

[Rijnsent]

Hi Hannahheileen,
you could use the Formula of a SeriesCollection to extract the sheetname of the series (and integrate that into your code), like so:

    Set Chrt = ActiveChart
    For Each srs In Chrt.SeriesCollection
        SheetName = srs.Formula
        SheetName = Right(SheetName, Len(SheetName) - 8)
        SheetName = Left(SheetName, InStr(SheetName, "!") - 1)
        Set Sht = Worksheets(SheetName)
    Next srs

Hope that helps,
Koen