CODEWITHSUNDEEP
vbaexcelcharts
L.Dutch
L.Dutch

Reputation: 966

Get chart name with VBA

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

Upvotes: 1

Views: 36599

Answers (5)

Grae Hunter
Grae Hunter

Reputation: 11

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"

Upvotes: 0

Bonny
Bonny

Reputation: 1

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.

Upvotes: 0

Shai Rado
Shai Rado

Reputation: 33682

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

Upvotes: 4

FunThomas
FunThomas

Reputation: 29276

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

Upvotes: 1

KoderM16
KoderM16

Reputation: 157

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!

Upvotes: 1

Related Questions