Chart Series Line: Bring to Front, Send to Back

Question

If I use VBA in Excel to make a line chart with multiple series, and two of the series' data are very similar so that their chart series lines partly overlap, the last one written is in front of earlier ones written.

In the Worksheet_Change event, I want to be able to go back and forth between which chart series line is in front, based on user actions that change the data. Can I do that without deleting and recreating the chart?

Here's how I'm identifiying the series line, for example here's series 2:

Sheet1.ChartObjects("MyChart").Chart.SeriesCollection(2)

Getting TypeName on that returns Series. I see Series in help, but with no information on its properties and methods. I don't see Series in the Object Browser (I'm on Excel 2007). I was able to get a list of properties and methods in the context help dropdown, but I didn't see anything promising in the dropdown.

So, can I bring a chart series to the front/send it to the back, without deleting and recreating the chart?

Answer 1

Here is a simple non-VBA way to highlight one series in a chart.

Below I show X data in column B, Y data for series alpha, beta, and gamma in columns C:E, and extra data in column F.

I have a chart, and below the chart a combo box inserted using Developer tab > Insert > Form Controls > Combo Box. I formatted the control (right click) and set the Input Range to K16:18, and the Cell Link to J16.

I selected F2:F16, with F2 being the active cell. I entered =INDEX(C2:E2,$J$16) in the formula bar, then held Ctrl while I pressed the Enter key. This filled the formula into the entire range. Essentially the formula takes the value from the three cells to the left, based on the Cell Link of the combo box. Since I've selected "beta" in the combo, J16 contains the value 2, and column F shows the values of the second data column.

The chart was made using the entire data range. The first three series were formatted with light gray lines, and the fourth (duplicate) series was formatted with a color that stands out in comparison. All data is visible at once, but only one is highlighted.

Answer 2

What you can do is change all the series line colors to a light color except for one. That one you color dark or bright, ie Red. Then you can repeat the process (assign the macro to a button on/near the chart) to highlight each series in turn.

    Sub CycleSeriesColors()
    Dim seriesCount As Integer, i As Integer, smod
    Static s    '"Remember" what series we are on between calls.
    ActiveSheet.ChartObjects("Chart 1").Activate
    '    ActiveChart.PlotArea.Select
    seriesCount = ActiveChart.SeriesCollection.Count
    s = s + 1
    smod = s Mod (seriesCount + 1)

    '    ActiveChart.ChartArea.Select
    If Not smod = 0 Then
        With ActiveChart.SeriesCollection(smod).Format.Line
            .Visible = msoTrue
            .Visible = msoTrue
            .ForeColor.RGB = RGB(192, 0, 0)
            .Transparency = 0
        End With
        For i = 1 To seriesCount
            If Not i = smod Then   'the series is to backgrounded
                With ActiveChart.SeriesCollection(i).Format.Line
                    .Visible = msoTrue
                    .Visible = msoTrue
                    .ForeColor.RGB = RGB(240, 240, 240)
                    .Transparency = 0.85
                End With
            End If
        Next i
    Else
    Randomize
       For i = 1 To seriesCount
            With ActiveChart.SeriesCollection(i).Format.Line
                .Visible = msoTrue
                .Visible = msoTrue
                .ForeColor.RGB = RGB(((i * i) ^ 0.6 * 3), ((i * i) ^ 0.6 * 8), ((i * i) ^ 0.6 * 12))
                .Transparency = 0
            End With
        Next i
    End If
    End Sub