excel - 数据透视图格式

Question

非常了解 VBA 的人，

我有一个透视图，我写了一个子来格式化系列表示。此图表包含四个系列并连接到切片器。

问题是这些格式不适用于某些切片器的按钮，因为其中一种系列格式消失了。这个系列格式应该是一条灰线；数据点在那里，但没有线条和填充颜色。

我已经调试了这个东西并使用 watch 检查发生了什么，但一切都很好，并且运行正常。当我 F8 宏时，在系列不起作用之后，我尝试使用鼠标强制图形线上的颜色并且它起作用。

你对我应该在哪里寻找问题有什么建议吗？这也发生在您的数据透视图上吗？

我写了这段代码:

Dim srs_name As String   
Dim srs As Integer


ActiveSheet.ChartObjects("Diagramm 7").Activate

         'formatting Shipped Qty series
srs_name = "Shipped qty"
On Error Resume Next
If Not ActiveChart.SeriesCollection(srs_name) Is Nothing Then
    ActiveChart.SeriesCollection(srs_name).ChartType = xlColumnStacked
    ActiveChart.SeriesCollection(srs_name).Format.Fill.ForeColor.RGB = RGB(255, 192, 0)
    ActiveChart.SeriesCollection(srs_name).Format.Line.ForeColor.RGB = RGB(255, 192, 0)
    ActiveChart.SeriesCollection(srs_name).AxisGroup = 1
End If

         'formatting Order series
srs_name = "Order"
On Error Resume Next
If Not ActiveChart.SeriesCollection(srs_name) Is Nothing Then
    ActiveChart.SeriesCollection(srs_name).ChartType = xlColumnStacked
    ActiveChart.SeriesCollection(srs_name).Format.Fill.ForeColor.RGB = RGB(91, 155, 213)
    ActiveChart.SeriesCollection(srs_name).Format.Line.ForeColor.RGB = RGB(91, 155, 213)
    ActiveChart.SeriesCollection(srs_name).AxisGroup = 1
End If

        'formatting Sales series
srs_name = "Sales"
On Error Resume Next
If Not ActiveChart.SeriesCollection(srs_name) Is Nothing Then
    ActiveChart.SeriesCollection(srs_name).ChartType = xlLine
    ActiveChart.SeriesCollection(srs_name).Format.Fill.ForeColor.RGB = RGB(165, 165, 165)
    ActiveChart.SeriesCollection(srs_name).Format.Line.ForeColor.RGB = RGB(165, 165, 165)
    ActiveChart.SeriesCollection(srs_name).AxisGroup = 1
End If

       'formatting Transport series
srs_name = "Transport"
On Error Resume Next
If Not ActiveChart.SeriesCollection(srs_name) Is Nothing Then
    ActiveChart.SeriesCollection(srs_name).ChartType = xlColumnStacked
    ActiveChart.SeriesCollection(srs_name).Format.Fill.ForeColor.RGB = RGB(237, 125, 49)
    ActiveChart.SeriesCollection(srs_name).Format.Line.ForeColor.RGB = RGB(237, 125, 49)
    ActiveChart.SeriesCollection(srs_name).AxisGroup = 1
Else
End If

       'formatting a 5th series, if needed
srs = ActiveSheet.ChartObjects("Diagramm 7").Chart.SeriesCollection.Count
If srs > 4 Then
    ActiveChart.SeriesCollection(5).ChartType = xlArea
    ActiveChart.SeriesCollection(5).Format.Fill.ForeColor.RGB = RGB(222, 235, 247)
    ActiveChart.SeriesCollection(5).Format.Line.ForeColor.RGB = RGB(222, 235, 247)
    ActiveChart.SeriesCollection(5).AxisGroup = 1
End If

Answer 1

已解决:我只需要添加一个 ActiveChart.SeriesCollection(srs_name).Format.Line.Visible = True 行。

它是这样得到的:

Dim srs_name As String   
Dim srs As Integer

ActiveSheet.ChartObjects("Diagramm 7").Activate

         'formatting Shipped Qty series
srs_name = "Shipped qty"
If Not ActiveChart.SeriesCollection(srs_name) Is Nothing Then
    ActiveChart.SeriesCollection(srs_name).ChartType = xlColumnStacked
    ActiveChart.SeriesCollection(srs_name).Format.Fill.ForeColor.RGB = RGB(255, 192, 0)
    ActiveChart.SeriesCollection(srs_name).Format.Line.ForeColor.RGB = RGB(255, 192, 0)
    ActiveChart.SeriesCollection(srs_name).AxisGroup = 1
End If

         'formatting Order series
srs_name = "Order"
If Not ActiveChart.SeriesCollection(srs_name) Is Nothing Then
    ActiveChart.SeriesCollection(srs_name).ChartType = xlColumnStacked
    ActiveChart.SeriesCollection(srs_name).Format.Fill.ForeColor.RGB = RGB(91, 155, 213)
    ActiveChart.SeriesCollection(srs_name).Format.Line.ForeColor.RGB = RGB(91, 155, 213)
    ActiveChart.SeriesCollection(srs_name).AxisGroup = 1
End If

        'formatting Sales series
srs_name = "Sales"
If Not ActiveChart.SeriesCollection(srs_name) Is Nothing Then
    ActiveChart.SeriesCollection(srs_name).ChartType = xlLine
    ActiveChart.SeriesCollection(srs_name).Format.Line.Visible = True
    ActiveChart.SeriesCollection(srs_name).Format.Fill.ForeColor.RGB = RGB(165, 165, 165)
    ActiveChart.SeriesCollection(srs_name).Format.Line.ForeColor.RGB = RGB(165, 165, 165)
    ActiveChart.SeriesCollection(srs_name).AxisGroup = 1
End If

       'formatting Transport series
srs_name = "Transport"
If Not ActiveChart.SeriesCollection(srs_name) Is Nothing Then
    ActiveChart.SeriesCollection(srs_name).ChartType = xlColumnStacked
    ActiveChart.SeriesCollection(srs_name).Format.Fill.ForeColor.RGB = RGB(237, 125, 49)
    ActiveChart.SeriesCollection(srs_name).Format.Line.ForeColor.RGB = RGB(237, 125, 49)
    ActiveChart.SeriesCollection(srs_name).AxisGroup = 1
Else
End If

       'formatting a 5th series, if needed
srs = ActiveSheet.ChartObjects("Diagramm 7").Chart.SeriesCollection.Count
If srs > 4 Then
    ActiveChart.SeriesCollection(5).ChartType = xlArea
    ActiveChart.SeriesCollection(5).Format.Fill.ForeColor.RGB = RGB(222, 235, 247)
    ActiveChart.SeriesCollection(5).Format.Line.ForeColor.RGB = RGB(222, 235, 247)
    ActiveChart.SeriesCollection(5).AxisGroup = 1
End If