Get chart name with VBA

Question

I am setting up a macro to generate a chart. I have recorded a macro while I was generating the sample chart, but now I need to have the macro working independently from the name of the chart (Chart 9 in this case)

Sheets("statistics").Select
Sheets("statistics").Range("A101:C106").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnStacked
ActiveChart.SetSourceData Source:=Range("statistics!$A$101:$C$106")
ActiveChart.ChartArea.Select
ActiveSheet.Shapes("Chart 9").Name = "waterfall"
ActiveChart.Location Where:=xlLocationAsObject, Name:="summary"
ActiveSheet.ChartObjects("waterfall").Activate
ActiveSheet.Shapes("waterfall").IncrementLeft 80
ActiveSheet.Shapes("waterfall").IncrementTop -2200
ActiveSheet.ChartObjects("waterfall").Activate
ActiveSheet.Shapes("waterfall").ScaleWidth 1.6025463692, msoFalse, msoScaleFromTopLeft
ActiveSheet.Shapes("waterfall").ScaleHeight 1.6084106153, msoFalse, msoScaleFromTopLeft
ActiveSheet.ChartObjects("waterfall").Activate
ActiveChart.Legend.Select
Selection.Delete
ActiveSheet.ChartObjects("waterfall").Activate
ActiveChart.SeriesCollection(1).Select
Selection.Format.Fill.Visible = msoFalse
ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).Points(6).Select
With Selection.Format.Fill
    .Visible = msoTrue
    .ForeColor.ObjectThemeColor = msoThemeColorAccent3
    .ForeColor.TintAndShade = 0
    .ForeColor.Brightness = 0
    .Solid
End With
ActiveChart.SeriesCollection(2).Points(1).Select
With Selection.Format.Fill
    .Visible = msoTrue
    .ForeColor.ObjectThemeColor = msoThemeColorAccent3
    .ForeColor.TintAndShade = 0
    .ForeColor.Brightness = 0
    .Solid
End With
With Selection.Format.Fill
    .Visible = msoTrue
    .ForeColor.ObjectThemeColor = msoThemeColorAccent1
    .ForeColor.TintAndShade = 0
    .ForeColor.Brightness = 0
    .Transparency = 0
    .Solid
End With
ActiveChart.SeriesCollection(2).Points(5).Select
With Selection.Format.Fill
    .Visible = msoTrue
    .ForeColor.ObjectThemeColor = msoThemeColorAccent1
    .ForeColor.TintAndShade = 0
    .ForeColor.Brightness = 0
    .Solid
End With
With Selection.Format.Fill
    .Visible = msoTrue
    .ForeColor.RGB = RGB(255, 0, 0)
    .Transparency = 0
    .Solid
End With
ActiveChart.SetElement (msoElementDataLabelCenter)
ActiveChart.SeriesCollection(2).Points(1).Select
ActiveChart.ChartArea.Select
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(2).Points(1).Select
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(2).Select
ActiveChart.SetElement (msoElementDataLabelCenter)
ActiveChart.SetElement (msoElementPrimaryValueAxisTitleHorizontal)
Selection.Caption = "hrs"
ActiveChart.Axes(xlValue).AxisTitle.Select
Selection.Left = 7
Selection.Top = 13.028

I have tried

Sheets("statistics").Range("A101:C106").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnStacked
ActiveChart.SetSourceData Source:=Range("statistics!$A$101:$C$106")
ActiveChart.ChartArea.Select
Set ThisChart = ActiveChart
ActiveSheet.Shapes(ThisChart).Name = "waterfall"

but it is not working

Are you creating just 1 chart per sheet or multiple charts?

Tim Wilkinson
– Tim Wilkinson

2017-02-22 11:59:35 +00:00
Commented Feb 22, 2017 at 11:59 — Tim Wilkinson
– Tim Wilkinson, Commented Feb 22, 2017 at 11:59
it's up to the user.

L.Dutch
– L.Dutch

2017-02-22 12:12:22 +00:00
Commented Feb 22, 2017 at 12:12 — L.Dutch
– L.Dutch, Commented Feb 22, 2017 at 12:12

Shai Rado · Accepted Answer · 2017-02-22 12:42:44Z

4

Try the code below, it will loop through all existing ChartObjects in "statistics" worksheet, and if it finds a chartobject with a name of "Chart 9" it will rename it to "waterfall".

Note: you could use a similar approach to create the chart, without the need to use Select, ActiveSheet and ActiveChart.

Code

Option Explicit

Sub RenameExistingChart()

Dim ChtObj As ChartObject

For Each ChtObj In Worksheets("statistics").ChartObjects
    If ChtObj.Name = "Chart 9" Then
        ChtObj.Name = "waterfall"
    End If
Next ChtObj

End Sub

Edit 1: create the chart with ChtObj:

Set ChtObj = Worksheets("statistics").ChartObjects.Add(Left:=100, Top:=100, _
                                    Width:=100, Height:=100) ' <-- just default settings , modify later    
With ChtObj
    .Chart.ChartType = xlColumnStacked
    .Chart.SetSourceData Source:=range("statistics!$A$101:$C$106")
    .Name = "waterfall"

    With .Chart.SeriesCollection(2).Format.Fill ' modify fill for series (2)
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent3
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
        .Solid
    End With

    .Chart.SeriesCollection(1).ApplyDataLabels ' add data lables to series (1)

End With

edited Feb 22, 2017 at 12:42

answered Feb 22, 2017 at 11:53

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

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

7 Comments

L.Dutch Over a year ago

maybe my question was not clear. I am editing it for the purpose.

Shai Rado Over a year ago

@L.Dutch what are you trying to do ? I guess I didn't understand what you are trying to achieve ?

L.Dutch Over a year ago

I want the macro to work on the chart it creates, independently from its name

L.Dutch Over a year ago

yes, now it creates the chart, but then it messes up the rest of the recorded macro, where all the ActiveChart fail

Shai Rado Over a year ago

@L.Dutch why do you want to use ActiveChart ?

|

KoderM16 · Accepted Answer · 2017-02-22 12:02:25Z

1

You could use something like this:

Sub ChartStuff()
Dim cht As Shape
Range("A101:A106").Select
ActiveSheet.Shapes.AddChart.Select
Set cht = ActiveSheet.Shapes(1)
cht.Name = "waterfall"
End Sub

Hope this helps!

answered Feb 22, 2017 at 12:02

KoderM16

1574 silver badges16 bronze badges

1 Comment

Tim Wilkinson Over a year ago

What if there is more than one chart per sheet?

FunThomas · Accepted Answer · 2017-02-22 12:19:16Z

1

Dealing with charts in VBA is a little complicated. When you use Addchart, Selection will be a ChartArea A ChartArea is part of a Chart which is part of a ChartObject The name of a chart you see is in fact the name of the ChartObject

You can do something like this:

Range("A101:A106").Select
ActiveSheet.Shapes.AddChart.Select

Dim ca As ChartArea, ch As Chart, co As ChartObject
Set ca = Selection 
Set ch = ca.Parent
ch.ChartType = xl3DColumn
Set co = ch.Parent
co.Name = "waterfall"
Debug.Print ca.Name, ch.Name, co.Name

answered Feb 22, 2017 at 12:19

FunThomas

31.3k4 gold badges24 silver badges40 bronze badges

2 Comments

Rory Over a year ago

It's actually very easy to get a reference to the chart directly: Set ch = ActiveSheet.Shapes.AddChart.Chart

FunThomas Over a year ago

You're right. It's confusing that the return value is a Chart while the Selection is a ChartArea...

Suraj Rao · Accepted Answer · 2018-12-10 12:47:33Z

0

Create a function that you call from within the sub, that sends the name of the active chart, for example:

Function actchart(ActiveChart As String) As String
  actchart = ActiveChart
End Function

And then from within your sub, you can replace, as an example, where you have:

ActiveSheet.Shapes("Chart 9").Name = "waterfall"

with

ActiveSheet.Shapes(actchart(ActiveChart.Parent.Name)).Name = "waterfall"

This worked for me with the same issue! Hope it helps.

edited Dec 10, 2018 at 12:47

Suraj Rao

29.7k11 gold badges96 silver badges104 bronze badges

answered Dec 10, 2018 at 12:41

Bonny

1

Comments

Grae Hunter · Accepted Answer · 2024-05-29 03:02:12Z

0

I was struggling with this while trying to remove the border for charts i was making with a macro - but I got the name with the following:

TargetName = ActiveSheet.ChartObjects.Item(1).Name

Then I could use that line so, acting on the chart I had just made:

ActiveSheet.Shapes(TargetChart).Line.Visible = msoFalse

So I would assume for you it would be:

TargetName = ActiveSheet.ChartObjects.Item(1).Name
ActiveSheet.Shapes(TargetName).Name = "waterfall"

answered May 29, 2024 at 3:02

Grae Hunter

112 bronze badges

Collectives™ on Stack Overflow

Get chart name with VBA

5 Answers 5

7 Comments

1 Comment

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

7 Comments

1 Comment

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related