Excel VBA取消选择图表系列的特定值

问题描述：

这对于这里的许多参与者来说应该是非常基本的.另外，这是我的第一个问题，对不起，如果我做错了事.

this should be pretty basic for many participants here. Also, this is my first question here so I'm sorry if i make something wrong.

我正在尝试从图表中取消选择特定值.问题是从图表中取消选择类别.基本上，从该图表中的所有系列开始.如果系列名称(值)之一等于"NotThisSeries"，则取消选择它.

I am trying to unselect a specific value from charts. Unselecting the category from the chart is the problem. Basically, from all the series in that chart. if one of the series name(Value) is iqual "NotThisSeries" then unselect it.

Sub exmpl()

Dim MySeries As Variant

ActiveSheet.ChartObjects("Chart 3").Activate

For Each MySeries In ActiveChart.SeriesCollection

  Select Case MySeries.XValues

    Case MySeries.Name = "NotThisSeries"

      ActiveChart.SeriesCollection(MySeries.Name).IsFiltered = True

    Case Else

  End Select

Next MySeries

End Sub

答

代码的主要问题是，当您似乎只需要一个简单的 If时，您正在使用 Select Case ..

The main issue with your code is that you are using a Select Case when you seem to just need a simple If.

更正后的代码是

Sub exmpl()

    Dim MySeries As Series
    ActiveSheet.ChartObjects("Chart 3").Activate

    For Each MySeries In ActiveChart.SeriesCollection
      If MySeries.Name = "NotThisSeries" Then
          ActiveChart.SeriesCollection(MySeries.Name).IsFiltered = True
        End If
    Next MySeries

End Sub

如果您想使用 Select Case 处理其他名称，这是正确的方法:

If you want to use the Select Case to handle other names, here is the correct way to do that:

Sub exmpl()

    Dim MySeries As Series
    ActiveSheet.ChartObjects("Chart 3").Activate

    For Each MySeries In ActiveChart.SeriesCollection
        Select Case MySeries.Name
            Case "NotThisSeries"
              ActiveChart.SeriesCollection(MySeries.Name).IsFiltered = True
        End Select
    Next MySeries

End Sub

编辑，这是隐藏类别而不是系列的相应代码.

Edit, here is the corresponding code to hide a category instead of a series.

Sub exmpl()

    Dim MySeries As Series
    ActiveSheet.ChartObjects("Chart 3").Activate

    Dim i As Integer
    Dim cat As ChartCategory

    For i = 1 To ActiveChart.ChartGroups(1).FullCategoryCollection.Count

        Set cat = ActiveChart.ChartGroups(1).FullCategoryCollection(i)

        If cat.Name = "NotThisSeries" Then
            cat.IsFiltered = True
        End If
    Next

End Sub

这是第二部分的另一个SO问题.查看对该问题的评论.使用VBA设置Excel图表过滤器

Here is another SO question that helped with the second part. See comments to the question. Set an excel chart filter with VBA

Excel VBA取消选择图表系列的特定值

相关推荐