Excel VBA取消选择图表系列的特定值 [英] excel vba unselect a specific value form a chart series

问题描述

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

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取消选择图表系列的特定值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！