naming new charts in vba code

bahllr

Board Regular
Joined
May 7, 2009
Messages
62
I have a set of code that will produce a new chart based on a set of data. I want to manipulate that chart (size etc) in VBA but am having a difficult time doing that when each new chart has a new name (Chart 1, 2, 3 etc).

I have found some code about naming the chart that I have tried unsuccessfully. Can anyone help me here?

This is what I have:

Range("B262:C267").Select
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Series Eval").Range("B262:C267"), _
PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="Analysis & Reports"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Capabilities by Series"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
.Name = Sheets("Analysis & Reports").Range("C3").Value
End With
ActiveChart.HasLegend = False
ActiveSheet.Shapes("Chart 39").IncrementLeft -215.25
ActiveSheet.Shapes("Chart 39").IncrementTop -84.75
ActiveSheet.Shapes("Chart 39").ScaleWidth 0.79, msoFalse, msoScaleFromTopLeft
ActiveSheet.Shapes("Chart 39").ScaleHeight 0.84, msoFalse, msoScaleFromTopLeft

I tried replacing "Chart 39" with the name, but that wasn't working for me either...as you can see by the chart number, I have been at this for awhile :)

Thanks
 

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
Code:
Dim myChart As Chart
Dim ChartName As String

    ChartName = Sheets("Analysis & Reports").Range("C3").Value
    
    Set myChart = Charts.Add
    With myChart
    
        .ChartType = xlColumnClustered
        .SetSourceData Source:=Sheets("Series Eval").Range("B262:C267"), _
                        PlotBy:=xlColumns
        .Location Where:=xlLocationAsObject, Name:="Analysis & Reports"
    End With
    
    Set myChart = ActiveChart
    With myChart
    
        .HasTitle = True
        .ChartTitle.Characters.Text = "Capabilities by Series"
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = False
        .Parent.Name = ChartName
        .HasLegend = False
    End With
    With ActiveSheet.Shapes(ChartName)
    
        .IncrementLeft -215.25
        .IncrementTop -84.75
        .ScaleWidth 0.79, msoFalse, msoScaleFromTopLeft
        .ScaleHeight 0.84, msoFalse, msoScaleFromTopLeft
    End With
 

Forum statistics

Threads
1,082,505
Messages
5,365,965
Members
400,864
Latest member
RobynP51

Some videos you may like

This Week's Hot Topics

Top