Search code examples
vbaexcel

VBA: Edit legend names from excel chart using VBA

I have made a chart where I try to plot some specific data. For that purpose I made this code:

Sub plotsim()
Dim sh As Worksheet
Dim chrt As Chart
worksh = Application.Sheets.Count
Set sh = ActiveWorkbook.Worksheets("Simulation")
Set chrt = sh.Shapes.AddChart.Chart
With chrt
    .ChartType = xlLine
    .SeriesCollection.NewSeries
    .SeriesCollection(1).Name = "=""Portfolio forecast"""
    .SeriesCollection(1).XValues = Sheets("Simulation").Range("A2:A" & fsize + 1)
    .SeriesCollection(1).Values = Sheets("Simulation").Range(Sheets("Simulation").Cells(2, worksh + 1), Sheets("Simulation").Cells(fsize + 1, worksh + 1))
End With
End Sub

But once I take a look at the chart, what I see is a legend with two different lines.

How can I remove (or edit) the Series2 title?

Solution

  • If you really wanted to edit Series2 in the legend you would change it the same manner you changed the name of Series1:

    .SeriesCollection(2).Name = "Unwanted series"

    enter image description here

    Note: I had originally answered with the following:

    The following line of code is adding your unwanted Series2:

    .SeriesCollection.NewSeries

    Simply remove it.

    But I see now that it was not entirely correct. Sometimes an additional one is created, sometimes not, depending on what has happened in the past on the sheet (I think I'm missing something obvious). I've been able to reproduce both behaviors. If you somehow end up with additional series', you could remove any superfluous ones with a call to:

    .SeriesCollection(2).Delete

    so long as you use the correct series number. You could include a test like this to see if an extra one has been made:

    If .SeriesCollection.Count > 1 then .SeriesCollection(2).Delete