CODEWITHSUNDEEP
excelvbachartsrename
Dave
Dave

Reputation: 3

Create and name a chart using VBA

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?

Upvotes: 0

Views: 2749

Answers (1)

Damian
Damian

Reputation: 5174

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

Upvotes: 1

Related Questions