文章/答案/技术大牛

发布

社区首页 >问答首页 >透视表图表格式

问透视表图表格式
EN

Stack Overflow用户

提问于 2019-09-18 21:59:44

回答 2查看 43关注 0票数 1

非常熟悉VBA的人，

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

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

我已经调试了这个东西，并使用手表检查发生了什么，但一切都很好，运行正常。当我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

excel

vba

format

excel-charts

pivot-chart

回答 2

Stack Overflow用户

回答已采纳

发布于 2019-09-19 19:21:54

解决方法:我只需要添加一条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

票数 1

Stack Overflow用户

发布于 2019-09-19 20:04:47

我认为如果系列名不存在，If Not ActiveChart.SeriesCollection(srs_name) Is Nothing Then会抛出异常。相反，我建议使用一个函数来测试是否存在SeriesExists。

另外，我建议不要使用Activate和ActiveChart (请参阅How to avoid using Select in Excel VBA)。取而代之的是通过其名称来指定图表。

请注意，如果存在5ᵗʰ系列，则不需要计算系列的数量来进行测试。您可以使用SeriesExists(MyChart, 5)来测试这一点。

我推荐下面这样的代码：

Option Explicit

Public Sub FormatCharts()
    Dim ws As Worksheet
    Set ws = ActiveSheet 'better something like ThisWorkbook.Worksheets("Tabelle 1")

    Dim MyChart As Chart
    Set MyChart = ws.ChartObjects("Diagramm 7").Chart

    Dim srs_name As String

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

   'formatting Order series
    srs_name = "Order"
    If SeriesExists(MyChart, srs_name) Then
        With MyChart.SeriesCollection(srs_name)
            .ChartType = xlColumnStacked
            .Format.Fill.ForeColor.RGB = RGB(91, 155, 213)
            .Format.Line.ForeColor.RGB = RGB(91, 155, 213)
            .AxisGroup = 1
        End With
    End If

    'formatting Sales series
    srs_name = "Sales"
    If SeriesExists(MyChart, srs_name) Then
        With MyChart.SeriesCollection(srs_name)
            .ChartType = xlLine
            .Format.Line.Visible = True
            .Format.Fill.ForeColor.RGB = RGB(165, 165, 165)
            .Format.Line.ForeColor.RGB = RGB(165, 165, 165)
            .AxisGroup = 1
        End With
    End If

    'formatting Transport series
    srs_name = "Transport"
    If SeriesExists(MyChart, srs_name) Then
        With MyChart.SeriesCollection(srs_name)
            .ChartType = xlColumnStacked
            .Format.Fill.ForeColor.RGB = RGB(237, 125, 49)
            .Format.Line.ForeColor.RGB = RGB(237, 125, 49)
            .AxisGroup = 1
        End With
    End If

    'formatting a 5th series, if needed
     If SeriesExists(MyChart, 5) Then 
        With MyChart.SeriesCollection(5)
            .ChartType = xlArea
            .Format.Fill.ForeColor.RGB = RGB(222, 235, 247)
            .Format.Line.ForeColor.RGB = RGB(222, 235, 247)
            .AxisGroup = 1
        End With
    End If
End Sub

Private Function SeriesExists(InChart As Chart, SeriesName As Variant) As Boolean
    Dim TestSeries As Series
    On Error Resume Next
    Set TestSeries = InChart.SeriesCollection(SeriesName)
    On Error GoTo 0
    SeriesExists = Not TestSeries Is Nothing
End Function

票数 1

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/57994544

复制

相似问题

问透视表图表格式
EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问透视表图表格式EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问透视表图表格式
EN