Customzing the Charts using VBA

Question

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:

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

means something like Set cht = ActiveChart is missing. You only declared cht as a chart but the variable is empty if you don't set it to some existing or new chart. — Pᴇʜ
– Pᴇʜ, Commented Jul 21, 2017 at 12:27
You never defined ptabel or ptr. Make sure you always put Option Explicit on the top of your programs to prevent these errors in the future — sourceCode
– sourceCode, Commented Jul 21, 2017 at 14:41

Dy.Lee · Accepted Answer · 2017-07-24 14:12:09Z

1

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

edited Jul 24, 2017 at 14:12

answered Jul 24, 2017 at 14:02

Dy.Lee

7,5471 gold badge14 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jenny Over a year ago

could you please provide me the colour code for the above graph , I have showed for green and Red

Dy.Lee Over a year ago

@Jenny: I modified my answer.

Dy.Lee Over a year ago

RGB is Red,Green,Blue of Light.

Collectives™ on Stack Overflow

Customzing the Charts using VBA

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related