Create and name a chart using VBA

Question

I am trying to create macro that will insert a simple bar chart in Excel. I have the following code which was a result of recording whilst I did it manually:

ActiveSheet.Shapes.AddChart2(286, xl3DColumnClustered).Select
    ActiveChart.SetSourceData Source:=Range("Dashboard!$T$8:$U$9")
    ActiveSheet.Shapes("Chart 2").IncrementLeft 2222.25
    ActiveSheet.Shapes("Chart 2").IncrementTop 25.5
    ActiveSheet.Shapes("Chart 2").ScaleWidth 0.5541666667, msoFalse, _
        msoScaleFromTopLeft
    ActiveSheet.Shapes("Chart 2").ScaleHeight 1.5416666667, msoFalse, _
        msoScaleFromTopLeft
    ActiveChart.Axes(xlValue).Select
    ActiveChart.Axes(xlValue).MinimumScale = 0
    Application.CommandBars("Format Object").Visible = False

This obviously creates, moves, resizes and sets the axis etc.

However, when I run the macro I get an error saying "The item with the specified name wasn't found"

Clearly it is looking for "Chart 2", however;

a) I don't know why it's called it Chart 2 (is there a way to set this when the chart is created?), and b) I can't work out how to subsequently set the name of the object.

I tried using:

ActiveChart.Name = "Chart Name"

But I then get an error saying "The specified dimension is not valid for the current chart type"

What am I doing wrong?

Damian · Accepted Answer · 2019-03-19 15:54:51Z

1

Chart 2 means is the second chart created, at least since you last reopened your workbook. You must use ActiveChart.Parent.Name = "Chart Name"

Edit: I usually Set the chart into a variable like this:

Dim Grafico As ChartObject
Set Grafico = ws.ChartObjects.Add(Left:=ws.Cells(13, 9).Left, _
    Width:=Ancho.Width / 2, _
    Top:=ws.Cells(13, 9).Top, _
    Height:=RangoGraficos.Height)
Grafico.Parent.Name = "Chart Object"

Variables are in Spanish but I believe is easy to understand

answered Mar 19, 2019 at 15:54

Damian

5,1741 gold badge12 silver badges22 bronze badges

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

2 Comments

Dave Over a year ago

This has worked, thank you. What does adding ".Parent" do compared to not having it?

Damian Over a year ago

That I should know, but I don't... sorry, most likely I was like you and found out the code working here somewhere.

Collectives™ on Stack Overflow

Create and name a chart using VBA

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related