Help 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

 

[Fluff]

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

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.