excel - (Excel VBA) 参数无效 (1004) 过滤后出错 - 动态图表

Question

我的动态图表有问题。每当我过滤一些信息时从我的表中，使用切片器或手动，Excel 向我发送一条错误消息。

参数无效 (1004) 错误在线:ChtObj.Chart.SeriesCollection(1).Values = _Range(Cells(UserRow, 12), Cells(UserRow, 21))

Sub UpdateChart()

Dim wb As Workbook
Dim ws As Worksheet
Dim ChtObj As ChartObject
Dim UserRow As Long

Set wb = ThisWorkbook
Set ws = wb.Sheets("Dashboard")
Set ChtObj = ws.ChartObjects("Chart 6")

UserRow = ActiveCell.Row
If UserRow < 27 Or IsEmpty(Cells(UserRow, 7)) Then
    ChtObj.Visible = False
Else
    ChtObj.Chart.SeriesCollection(1).Values = _
       Range(Cells(UserRow, 12), Cells(UserRow, 21))
    ChtObj.Chart.ChartTitle.Text = Cells(UserRow, 7).Text
    ChtObj.Visible = True
End If
End Sub

我在仪表板表中调用此函数

Private Sub CheckBox1_Click()
    If CheckBox1 Then
        Call UpdateChart
        ActiveCell.Select
    End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If CheckBox1 Then Call UpdateChart
End Sub

你能帮我一下吗？

Answer 1

更换线路:

ChtObj.Chart.SeriesCollection(1).Values = _
       Range(Cells(UserRow, 12), Cells(UserRow, 21))

与:

ChtObj.Chart.SeriesCollection(1).Values = _
       "=" & ws.Range(ws.Cells(UserRow, 12), ws.Cells(UserRow, 21)).Address(0, 0, xlA1, xlExternal)