CODEWITHSUNDEEP
vbaexcelexcel-charts
Jenny
Jenny

Reputation: 451

Customzing the Charts using VBA

I have a sheet with Pivot table and I am creating an Column stacked chart from the table.

I would like to change the colours of Legend entries in chart and would like to have an title for the chart every time I create the chart.

I tried the below code and I'm getting

Object variable or with block variable not set

I get the error in the line 

cht.SeriesCollection(1).Format.Fill.ForeColor.RGB = RGB(155, 213, 91)

Could anyone, tell me how I could create chart for my requirements. I have added an image of my current chart and required chart.

Sub chart11()
    Dim sh As Shape
    Dim cht As Chart

    If ActiveSheet.PivotTables.Count = 0 Then Exit Sub

    Set ptable = ActiveSheet.PivotTables(1)
    Set ptr = ptable.TableRange1
    Set sh = ActiveSheet.Shapes.AddChart
    sh.Select

    With ActiveChart
        .SetSourceData ptr
        .ChartType = xlColumnStacked
    End With

    cht.SeriesCollection(1).Format.Fill.ForeColor.RGB = RGB(155, 213, 91)
    cht.Axes(xlCategory).MinimumScale = 5
    cht.Axes(xlCategory).MaximumScale = 40
    cht.HasTitle = True    
    cht.ChartTitle.Text = "Default Chart"
End Sub

This is the chart generated by my code now, if I comment the error line:

This is the chart generated by my code now, if I comment the error line.

I would like to have a chart of this type with change in colour legends and chart title:

I would like to have a chart of this type with change in colour legends and chart title

Upvotes: 0

Views: 183

Answers (1)

Dy.Lee
Dy.Lee

Reputation: 7567

You can't use cht.Axes(xlCategory).MinimumScale = 5 and MaximumScale. Because Pivot chart do not assist Scatterchart. Only in scatter chart, you can set xlCategory Scale.

Sub chart11()
    Dim sh As Shape
    Dim cht As Chart

    If ActiveSheet.PivotTables.Count = 0 Then Exit Sub

    Set ptable = ActiveSheet.PivotTables(1)
    Set ptr = ptable.TableRange1
    Set sh = ActiveSheet.Shapes.AddChart
    sh.Select
    Set cht = ActiveChart
    With cht
        .SetSourceData ptr
        .ChartType = xlColumnStacked
    End With

    cht.SeriesCollection(1).Format.Fill.ForeColor.RGB = RGB(255, 0, 0) '<~~ Red
    cht.SeriesCollection(2).Format.Fill.ForeColor.RGB = RGB(0, 255, 0) '<~~ green
    'cht.Axes(xlCategory).MinimumScale = 5
    'cht.Axes(xlCategory).MaximumScale = 40
    cht.HasTitle = True
    cht.ChartTitle.Text = "Default Chart"
End Sub

Upvotes: 1

Related Questions