VBA按钮控制工作表所有图表系列显隐的报错及弹窗问题求助

编写宏的目标是：在单元格输入指定数值后，通过按钮触发宏，对工作簿内所有工作表的所有图表中同名数据系列统一执行隐藏/取消隐藏操作。此前代码可正常运行，现在运行时弹出Run-time error '1004': Parameter not valid（运行时错误'1004'：参数无效），报错位置为代码行.FullSeriesCollection("Bottom A1").IsFiltered = False。另外设置了输入无效数值（大于8的数值）时弹出提示框，但目前提示框需要多次点击才能关闭，需要实现点击一次OK即可关闭提示的效果。

Public Sub UseValue()
For Each sht In ActiveWorkbook.Worksheets
    For Each ChObj In sht.ChartObjects
        With ChObj.Chart
        If Range("AE2").Value = 1 Then
            .FullSeriesCollection("Bottom A1").IsFiltered = False
            .FullSeriesCollection("Delta Y1").IsFiltered = False
            .FullSeriesCollection("Bottom A2").IsFiltered = True
            .FullSeriesCollection("Delta Y2").IsFiltered = True
            .FullSeriesCollection("Bottom A3").IsFiltered = True
            .FullSeriesCollection("Delta Y3").IsFiltered = True
            .FullSeriesCollection("Bottom A4").IsFiltered = True
            .FullSeriesCollection("Delta Y4").IsFiltered = True
            .FullSeriesCollection("Bottom A5").IsFiltered = True
            .FullSeriesCollection("Delta Y5").IsFiltered = True
            .FullSeriesCollection("Bottom A6").IsFiltered = True
            .FullSeriesCollection("Delta Y6").IsFiltered = True
            .FullSeriesCollection("Bottom A7").IsFiltered = True
            .FullSeriesCollection("Delta Y7").IsFiltered = True
            .FullSeriesCollection("Bottom A8").IsFiltered = True
            .FullSeriesCollection("Delta Y8").IsFiltered = True
        ElseIf Range("AE2").Value = 2 Then
            .FullSeriesCollection("Bottom A1").IsFiltered = False
            .FullSeriesCollection("Delta Y1").IsFiltered = False
            .FullSeriesCollection("Bottom A2").IsFiltered = False
            .FullSeriesCollection("Delta Y2").IsFiltered = False
            .FullSeriesCollection("Bottom A3").IsFiltered = True
            .FullSeriesCollection("Delta Y3").IsFiltered = True
            .FullSeriesCollection("Bottom A4").IsFiltered = True
            .FullSeriesCollection("Delta Y4").IsFiltered = True
            .FullSeriesCollection("Bottom A5").IsFiltered = True
            .FullSeriesCollection("Delta Y5").IsFiltered = True
            .FullSeriesCollection("Bottom A6").IsFiltered = True
            .FullSeriesCollection("Delta Y6").IsFiltered = True
            .FullSeriesCollection("Bottom A7").IsFiltered = True
            .FullSeriesCollection("Delta Y7").IsFiltered = True
            .FullSeriesCollection("Bottom A8").IsFiltered = True
            .FullSeriesCollection("Delta Y8").IsFiltered = True
        ElseIf Range("AE2").Value = 3 Then
            .FullSeriesCollection("Bottom A1").IsFiltered = False
            .FullSeriesCollection("Delta Y1").IsFiltered = False
            .FullSeriesCollection("Bottom A2").IsFiltered = False
            .FullSeriesCollection("Delta Y2").IsFiltered = False
            .FullSeriesCollection("Bottom A3").IsFiltered = False
            .FullSeriesCollection("Delta Y3").IsFiltered = False
            .FullSeriesCollection("Bottom A4").IsFiltered = True
            .FullSeriesCollection("Delta Y4").IsFiltered = True
            .FullSeriesCollection("Bottom A5").IsFiltered = True
            .FullSeriesCollection("Delta Y5").IsFiltered = True
            .FullSeriesCollection("Bottom A6").IsFiltered = True
            .FullSeriesCollection("Delta Y6").IsFiltered = True
            .FullSeriesCollection("Bottom A7").IsFiltered = True
            .FullSeriesCollection("Delta Y7").IsFiltered = True
            .FullSeriesCollection("Bottom A8").IsFiltered = True
            .FullSeriesCollection("Delta Y8").IsFiltered = True
        ElseIf Range("AE2").Value = 4 Then
            .FullSeriesCollection("Bottom A1").IsFiltered = False
            .FullSeriesCollection("Delta Y1").IsFiltered = False
            .FullSeriesCollection("Bottom A2").IsFiltered = False
            .FullSeriesCollection("Delta Y2").IsFiltered = False
            .FullSeriesCollection("Bottom A3").IsFiltered = False
            .FullSeriesCollection("Delta Y3").IsFiltered = False
            .FullSeriesCollection("Bottom A4").IsFiltered = False
            .FullSeriesCollection("Delta Y4").IsFiltered = False
            .FullSeriesCollection("Bottom A5").IsFiltered = True
            .FullSeriesCollection("Delta Y5").IsFiltered = True
            .FullSeriesCollection("Bottom A6").IsFiltered = True
            .FullSeriesCollection("Delta Y6").IsFiltered = True
            .FullSeriesCollection("Bottom A7").IsFiltered = True
            .FullSeriesCollection("Delta Y7").IsFiltered = True
            .FullSeriesCollection("Bottom A8").IsFiltered = True
            .FullSeriesCollection("Delta Y8").IsFiltered = True
        ElseIf Range("AE2").Value = 5 Then
            .FullSeriesCollection("Bottom A1").IsFiltered = False
            .FullSeriesCollection("Delta Y1").IsFiltered = False
            .FullSeriesCollection("Bottom A2").IsFiltered = False
            .FullSeriesCollection("Delta Y2").IsFiltered = False
            .FullSeriesCollection("Bottom A3").IsFiltered = False
            .FullSeriesCollection("Delta Y3").IsFiltered = False
            .FullSeriesCollection("Bottom A4").IsFiltered = False
            .FullSeriesCollection("Delta Y4").IsFiltered = False
            .FullSeriesCollection("Bottom A5").IsFiltered = False
            .FullSeriesCollection("Delta Y5").IsFiltered = False
            .FullSeriesCollection("Bottom A6").IsFiltered = True
            .FullSeriesCollection("Delta Y6").IsFiltered = True
            .FullSeriesCollection("Bottom A7").IsFiltered = True
            .FullSeriesCollection("Delta Y7").IsFiltered = True
            .FullSeriesCollection("Bottom A8").IsFiltered = True
            .FullSeriesCollection("Delta Y8").IsFiltered = True
        ElseIf Range("AE2").Value = 6 Then
            .FullSeriesCollection("Bottom A1").IsFiltered = False
            .FullSeriesCollection("Delta Y1").IsFiltered = False
            .FullSeriesCollection("Bottom A2").IsFiltered = False
            .FullSeriesCollection("Delta Y2").IsFiltered = False
            .FullSeriesCollection("Bottom A3").IsFiltered = False
            .FullSeriesCollection("Delta Y3").IsFiltered = False
            .FullSeriesCollection("Bottom A4").IsFiltered = False
            .FullSeriesCollection("Delta Y4").IsFiltered = False
            .FullSeriesCollection("Bottom A5").IsFiltered = False
            .FullSeriesCollection("Delta Y5").IsFiltered = False
            .FullSeriesCollection("Bottom A6").IsFiltered = False
            .FullSeriesCollection("Delta Y6").IsFiltered = False
            .FullSeriesCollection("Bottom A7").IsFiltered = True
            .FullSeriesCollection("Delta Y7").IsFiltered = True
            .FullSeriesCollection("Bottom A8").IsFiltered = True
            .FullSeriesCollection("Delta Y8").IsFiltered = True
        ElseIf Range("AE2").Value = 7 Then
            .FullSeriesCollection("Bottom A1").IsFiltered = False
            .FullSeriesCollection("Delta Y1").IsFiltered = False
            .FullSeriesCollection("Bottom A2").IsFiltered = False
            .FullSeriesCollection("Delta Y2").IsFiltered = False
            .FullSeriesCollection("Bottom A3").IsFiltered = False
            .FullSeriesCollection("Delta Y3").IsFiltered = False
            .FullSeriesCollection("Bottom A4").IsFiltered = False
            .FullSeriesCollection("Delta Y4").IsFiltered = False
            .FullSeriesCollection("Bottom A5").IsFiltered = False
            .FullSeriesCollection("Delta Y5").IsFiltered = False
            .FullSeriesCollection("Bottom A6").IsFiltered = False
            .FullSeriesCollection("Delta Y6").IsFiltered = False
            .FullSeriesCollection("Bottom A7").IsFiltered = False
            .FullSeriesCollection("Delta Y7").IsFiltered = False
            .FullSeriesCollection("Bottom A8").IsFiltered = True
            .FullSeriesCollection("Delta Y8").IsFiltered = True
        ElseIf Range("AE2").Value = 8 Then
            .FullSeriesCollection("Bottom A1").IsFiltered = False
            .FullSeriesCollection("Delta Y1").IsFiltered = False
            .FullSeriesCollection("Bottom A2").IsFiltered = False
            .FullSeriesCollection("Delta Y2").IsFiltered = False
            .FullSeriesCollection("Bottom A3").IsFiltered = False
            .FullSeriesCollection("Delta Y3").IsFiltered = False
            .FullSeriesCollection("Bottom A4").IsFiltered = False
            .FullSeriesCollection("Delta Y4").IsFiltered = False
            .FullSeriesCollection("Bottom A5").IsFiltered = False
            .FullSeriesCollection("Delta Y5").IsFiltered = False
            .FullSeriesCollection("Bottom A6").IsFiltered = False
            .FullSeriesCollection("Delta Y6").IsFiltered = False
            .FullSeriesCollection("Bottom A7").IsFiltered = False
            .FullSeriesCollection("Delta Y7").IsFiltered = False
            .FullSeriesCollection("Bottom A8").IsFiltered = False
            .FullSeriesCollection("Delta Y8").IsFiltered = False
        Else
            MsgBox ("Please input layer(s) from 1 to 8 !")
        End If
        End With
    Next ChObj
Next sht

End Sub
Private Sub CommandButton2_Click()

End Sub

• 1004参数无效报错：一是部分图表内不存在名为Bottom A1或对应Delta Y*的系列，直接按名称调用FullSeriesCollection就会触发参数错误；二是代码中Range("AE2")未指定所属工作表，跨工作表循环时可能出现取值异常，导致逻辑分支判断错误。
• 提示框重复弹出：提示框代码写在了工作表+图表对象的两层循环内部，每遍历到一个图表对象就会触发一次弹窗，因此需要多次点击才能关闭。

修复逻辑 #

• 把输入值读取、合法性校验移到循环外部，输入不合法时仅弹一次提示就直接终止程序，解决重复弹窗问题
• 操作系列前先判断系列是否存在，不存在则跳过，避免直接调用不存在的系列触发1004错误
• 用循环替代大段重复的系列赋值代码，精简代码结构，减少后续维护成本
• 显式声明所有变量，避免隐式变量导致的未知错误

修复后代码 #

Public Sub UseValue()
    Dim sht As Worksheet
    Dim ChObj As ChartObject
    Dim inputVal As Integer
    Dim i As Integer
    Dim ser As Series
    
    ' 读取输入值，若AE2不在当前活动工作表，请将ActiveSheet替换为对应工作表名称，例如Sheets("参数页")
    inputVal = CInt(ThisWorkbook.ActiveSheet.Range("AE2").Value)
    
    ' 提前做输入校验，不合法直接弹一次提示退出
    If inputVal < 1 Or inputVal > 8 Then
        MsgBox "Please input layer(s) from 1 to 8 !", vbExclamation
        Exit Sub
    End If
    
    ' 遍历所有工作表、所有图表
    For Each sht In ActiveWorkbook.Worksheets
        For Each ChObj In sht.ChartObjects
            With ChObj.Chart
                For i = 1 To 8
                    ' 操作Bottom A系列
                    On Error Resume Next
                    Set ser = .FullSeriesCollection("Bottom A" & i)
                    On Error GoTo 0
                    If Not ser Is Nothing Then
                        ' 序号小于等于输入值则显示，否则隐藏
                        ser.IsFiltered = Not (i <= inputVal)
                        Set ser = Nothing
                    End If
                    
                    ' 操作Delta Y系列
                    On Error Resume Next
                    Set ser = .FullSeriesCollection("Delta Y" & i)
                    On Error GoTo 0
                    If Not ser Is Nothing Then
                        ser.IsFiltered = Not (i <= inputVal)
                        Set ser = Nothing
                    End If
                Next i
            End With
        Next ChObj
    Next sht
End Sub

Private Sub CommandButton2_Click()
    Call UseValue
End Sub

• 如果AE2单元格不在触发按钮所在的工作表，请将读取输入值代码中的ActiveSheet替换为实际存放输入值的工作表对象，避免取值错误
• 代码增加了系列存在性判断，即使个别图表缺失部分系列，也不会弹出1004错误，会自动跳过不存在的系列
• 若工作簿存在图表工作表（Chart Sheet）而非嵌入在工作表中的图表对象，可额外增加Chart类型工作表的遍历逻辑即可

内容的提问来源于stack exchange，提问作者Sri Kanti