使用VBA删除具有空白系列名称的图例条目 [英] Deleting legend entries with blank series name using VBA

问题描述

我正在尝试添加一个子例程，该子例程将删除折线图中带有空白名称的系列的图例条目。禁用相关系列时，会出现空白名称，这是通过另一个例程完成的。

I'm trying to add in a subroutine that will delete the legend entries for series with blank names, in a line graph. Blank names arise when the series in question is disabled, this is done via another routine.

我一直在使用下面的代码，但是最终我丢失了没有空白名称的系列的图例条目，而且我不确定在哪里错了吗？供参考，我总共有五个系列。

I've been using the code below but I end up losing the legend entries for series that don't have blank names, and I'm unsure of where I'm going wrong? For reference I have five series in total.

  Sub LegendRemover()
  Dim i As Integer
  Dim chtob As ChartObject
  On Error Resume Next

  Call Open_Results

      Set chtob = ActiveSheet.ChartObjects("Chart 8")
      For i = chtob.Chart.SeriesCollection.Count To 1 Step -1
      If chtob.Chart.SeriesCollection(i).Name = "" Then
          chtob.Chart.Legend.LegendEntries(i).Delete
        End If
      Next i

End Sub

预先感谢！

推荐答案

这里是问题，当代码第一次运行时，它可以工作。

here's the issue, when the code runs the first time, it works.

但是，由于您删除了图例条目，因此
个图例条目的数量不再与系列集合的数量匹配。

however, since you've deleted legend entries, the count of legend entries no longer matches the count of series collections.

这是一个视觉上，假设系列2的名称为空白，第一次运行前为
...

here's a visual, let's say the name for series 2 is blank, before first run...

series blank legend
1      n     1
2      y     2
3      n     3
4      n     4

第一次运行后，由于删除了图例2，现在3变为2。

after the first run, since legend 2 was deleted, 3 now becomes 2.

series blank legend
1      n     1
2      y     2
3      n     3
4      n

下次运行时，原始图例3（现在为2）被删除，依此类推...

on the next run, original legend 3, now 2, is deleted, and so on...

series blank legend
1      n     1
2      y     2
3      n
4      n

---

而不是删除文件gend，

您可以在该系列上使用 IsFiltered 属性。

当 IsFiltered 是真实的，行和图例都将被隐藏，但不会被删除。

，如果重新添加名称，则可以稍后显示...

---

rather than deleting the legend,
you can use the IsFiltered property on the series.
when IsFiltered is true, both the line and legend will be hidden, but not removed.
and can be shown later, if the name is added back...

请参阅以下代码段...

see following snippet...

Sub LegendRemover()
  Dim chtob As ChartObject
  Dim seriesLine As series

  Set chtob = ActiveSheet.ChartObjects("Chart 8")
  For Each seriesLine In chtob.Chart.SeriesCollection
    seriesLine.IsFiltered = (seriesLine.Name = "")
  Next seriesLine
End Sub

这篇关于使用VBA删除具有空白系列名称的图例条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！