VBA Chart.SetSourceData 正在切换我的系列和类别

Question

我已经为此投入了相当多的时间，但似乎找不到解决方案。

我正在使用工作表选择更改事件，该事件允许人们修改图表元素，而无需打开图表格式选项。当我尝试使用时出现问题

.Chart.SetSourceData Union(rng0,rng5)

进一步解释一下..我的图表有 5 个系列和 4 列数据:
rng0 包含 series1-5 的标题，
rng1 包含系列 1-5 第一列数据的数据，
rng2 包含系列 1-5 第二列数据的数据，等等..

我允许用户决定他们希望图表显示哪一系列数据(rng1-4)。用户通过工作表选择框进行选择，一旦选择，该选择框就会变成复选框。然后，我测试每个选项以确定要在图表中包含哪些数据系列。在所有情况下，数据都是不连续的，因为 rng0 距离 rng1-4 多了几列。我已经测试过这是否重要，但并不重要。我使用 Union(rng5,rng1-4) 来组合所有选定的范围。

union(rng0,rng5) 确实有效，但它最终交换了我的轴。我尝试了几件事，包括:

myChart.PlotBy = xlRows  'This does not change the chart at all

我也搞乱了 application.commandbars，但我找不到转到图表的按钮工具 -> 设计 -> 切换行/列。

下面是该区域的图片，下面是代码。我感谢您的任何帮助/想法。如果您需要更多信息，请告诉我。谢谢

/image/mOnjP.jpg

这是代码的子集，因为它要大得多，并且包含一些与此问题无关的其他内容。

        Dim myChart As ChartObject
        Dim rng0 As Range
        Dim rng1 As Range
        Dim rng2 As Range
        Dim rng3 As Range
        Dim rng4 As Range
        Dim rng5 As Range

        'Slide area selected, set new subranges
        Set singleChart1 = Range("OTPSingleChart1Sel")
        'Test which chart is being modified
        'Single Chart1

        If Not Intersect(Target, singleChart1) Is Nothing Then
            Set myChart = otpWS.ChartObjects("OTPSingleChart1")
            With otpWS
                Set rng0 = Range("OTPSingleChart1Sel").Offset(0, 5)
                Set rng1 = Range("OTPSingleChart1Sel").Offset(0, 7)
                Set rng2 = Range("OTPSingleChart1Sel").Offset(0, 8)
                Set rng3 = Range("OTPSingleChart1Sel").Offset(0, 9)
                Set rng4 = Range("OTPSingleChart1Sel").Offset(0, 10)
            End With
            'Test offset
            'D2P
            If Target.Offset(0, 2).Value = "D2P" Then
                If Target.Value = "a" Then
                 'Test if at least one of our segments is on
                    With otpWS
                        If Application.WorksheetFunction.CountIf(.Range("OTPSingleChart1Sel").Range(.Cells(1, 1), .Cells(4, 1)), "a") > 1 Then
                        Else
                            MsgBox ("At least one segment must be visible")
                            Exit Sub
                        End If
                    End With
                    Target.Value = ""
                    With otpWS
                        i = 1
                        For Each chk In .Range("OTPSingleChart1Sel").Range(.Cells(1, 1), .Cells(4, 1))
                            If chk.Value = "a" Then
                                If rng5 Is Nothing Then
                                    If i = 1 Then
                                        Set rng5 = rng1
                                    ElseIf i = 2 Then
                                        Set rng5 = rng2
                                    ElseIf i = 3 Then
                                        Set rng5 = rng3
                                    ElseIf i = 4 Then
                                        Set rng5 = rng4
                                    End If
                                Else
                                    If i = 1 Then
                                        Set rng5 = Union(rng5, rng1)
                                    ElseIf i = 2 Then
                                        Set rng5 = Union(rng5, rng2)
                                    ElseIf i = 3 Then
                                        Set rng5 = Union(rng5, rng3)
                                    ElseIf i = 4 Then
                                        Set rng5 = Union(rng5, rng4)
                                    End If
                                End If
                            End If
                        i = i + 1
                        Next chk
                    End With
                    With myChart
                        .Chart.SetSourceData Union(rng0, rng5)
                    End With

Answer 1

评论里回答

You mentioned you have used PlotBy, have you used it within SetSourceData? i.e. Chart.SetSourceData Union(rng0, rng5), xlRows