VBA creating chart error

Question

I am trying to create a chart from pivot table but I am getting an error. My code is given below:

Sub Chart()
'
' chart Macro
  Dim shp As Chart

'
    Set shp = Charts.Add
    Worksheets("pivot").Select
    Range("B5:E5").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=Range("Pivot!$A$3:$E$5")
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Tools Sold"
    With ActiveChart
    .HasTitle = True
    .ChartTitle.Characters.Text = "Consolidated"

    ActiveSheet.Shapes("charts").LockAspectRatio = msoTrue
    ActiveChart.ShowValueFieldButtons = False
    ActiveSheet.ChartObjects("charts").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).ApplyDataLabels
    ActiveChart.SeriesCollection(2).Select
    ActiveChart.SeriesCollection(2).ApplyDataLabels
    ActiveChart.SeriesCollection(3).Select
    ActiveChart.SeriesCollection(3).ApplyDataLabels

    End With
End Sub

I got a run time error when I debugging line

ActiveSheet.Shapes("charts").LockAspectRatio = msoTrue

I am a beginner. So not be able to resolve this issue. I am also attached the error screenshot and the line. How to resolve this issue?

Probably your chart (and therefore the ShapeObject) is not named "Chart" — FunThomas
– FunThomas, Commented Aug 10, 2018 at 10:45
@FunThomas - are charts ever referred to as a ShapeObject? I thought they were referred to as ChartObjects as in ChartObjects("Chart 1")? — ashleedawg
– ashleedawg, Commented Aug 10, 2018 at 10:50
@ashleedawg: The macro recorder does so if you set the LockAspectRatio (and I couldn't find a way on the quick to do the same with the Chart Object) — FunThomas
– FunThomas, Commented Aug 10, 2018 at 10:54
Click on your chart and see to the left of formula bar to check the name of the chart. — Imran Malek
– Imran Malek, Commented Aug 10, 2018 at 10:56

Imran Malek · Accepted Answer · 2018-08-10 11:16:08Z

2

Add these lines before the error message and refer to your chart with the name given with cht.Name

Dim cht As Shape
Set cht = ActiveSheet.Shapes(1)
    cht.Name = "chart001"

answered Aug 10, 2018 at 11:16

Imran Malek

1,7392 gold badges13 silver badges14 bronze badges

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

Comments

ashleedawg · Accepted Answer · 2018-08-10 11:05:02Z

I would suggest that when you're trying to automate any of Excel's built-in tasks, instead of trying to figure out the exact VBA yourself, you use the Macro Recorder to record your actions as you do the steps manually (in this case, create a chart from a PivotTable), and then you and view and edit the generated code as required.

Edit:

If you're not sure what your chart is named, one way to find out is by running this:

Sub ListCharts()
    Dim x
    For Each x In ActiveSheet.ChartObjects
      Debug.Print x.Name
    Next x
End Sub

Use Ctrl+G to open the Immediate Window to view the results (if it's not already open).

More Information:

DisplayName · Accepted Answer · 2018-08-10 13:53:54Z

0

you don't need neither any shape object nor all that activating/selecting

With Worksheets("pivot").Shapes.AddChart.Chart
    .ChartType = xlColumnClustered
    .SetSourceData Source:=Range("Pivot!$A$3:$E$5")
    .HasTitle = True
    .ChartTitle.Characters.Text = "Consolidated"
    .SeriesCollection(1).ApplyDataLabels
    .SeriesCollection(2).ApplyDataLabels
    .SeriesCollection(3).ApplyDataLabels
    .Location Where:=xlLocationAsNewSheet, Name:="Tools Sold"
End With

answered Aug 10, 2018 at 13:53

DisplayName

13.4k2 gold badges13 silver badges19 bronze badges

1 Comment

DisplayName Over a year ago

Even if you went on and changed your code, you may want to give proper feedback and close this question. Thank you,

Collectives™ on Stack Overflow

VBA creating chart error

3 Answers 3

Comments

Edit:

More Information:

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Edit:

More Information:

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related