vba 图表系列线：放在前面，放在后面

Chart Series Line: Bring to Front, Send to Back

提问by Greg Lovern

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.

如果我在 Excel 中使用 VBA 制作具有多个系列的折线图，并且其中两个系列的数据非常相似，以至于它们的图表系列线部分重叠，则最后写入的在前面写入的前面。

In the Worksheet_Changeevent, 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?

在这种情况Worksheet_Change下，我希望能够根据更改数据的用户操作在前面的图表系列线之间来回切换。我可以在不删除和重新创建图表的情况下做到这一点吗？

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

这是我如何识别系列线，例如这是系列 2：

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

Getting TypeNameon that returns Series. I see Seriesin help, but with no information on its properties and methods. I don't see Seriesin 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.

获取TypeName上的回报Series。我Series在帮助中看到，但没有关于其属性和方法的信息。我Series在对象浏览器中看不到（我使用的是 Excel 2007）。我能够在上下文帮助下拉列表中获得属性和方法列表，但我在下拉列表中没有看到任何有希望的内容。

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

那么，我可以在不删除和重新创建图表的情况下将图表系列放在前面/将其发送到后面吗？

回答by Jon Peltier

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

这是在图表中突出显示一个系列的简单非 VBA 方法。

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.

下面我在 B 列中显示 X 数据，在 C:E 列中显示系列 alpha、beta 和 gamma 的 Y 数据，以及在 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.

我有一个图表，在图表下方使用开发工具选项卡 > 插入 > 表单控件 > 组合框插入了一个组合框。我格式化了控件（右键单击）并将输入范围设置为 K16:18，将单元格链接设置为 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.

我选择了 F2:F16，其中 F2 是活动单元格。我=INDEX(C2:E2,$J$16)在公式栏中输入，然后在按下 Enter 键的同时按住 Ctrl。这将公式填充到整个范围内。本质上，该公式根据组合框的单元格链接从左侧的三个单元格中获取值。由于我在组合中选择了“beta”，因此 J16 包含值 2，F 列显示第二个数据列的值。

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.

该图表是使用整个数据范围制作的。前三个系列用浅灰色线条格式化，第四个（重复）系列用比较突出的颜色格式化。所有数据一次可见，但只有一个被突出显示。

回答by klausnrooster

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