Naming an Excel Chart Object

[mep1]

Hi,
I am trying to edit some macro code written by a co-worker involving excel graph objects. The macro scans the data and creates a chart. I have inserted the following line to rename the chart so that I can access it later.

ActiveChart.Name = assays(i) & controlRange

After updating the chart, it is moved to a seperate sheet as an object. When it is moved, the chart name is changed to "Summary for Export Chart 2217" and I am unable to edit the name. The code that moves the chart and updates the chart properties is given below.

ActiveChart.Location Where:=xlLocationAsObject, Name:="Summary for Export"

With ActiveChart
.HasTitle = True
.chartTitle.Characters.Text = chartTitle & "" & addString
'.Name = addString
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Date"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Titer"
.Axes(xlCategory).Select
Selection.TickLabels.NumberFormat = "[$-409]d-mmm-yy;@"
Selection.TickLabels.Font.Size = 7
End With

The macro creates 18 charts and I would like the access them by name if possible. Any help on manipulating chart objects in a sheet would be great.

Thanks,
mep

 

[SQLBI]

Hi,

You could navigate through each of a workbooks charts using

Dim cht As Chart

    For Each cht In ActiveWorkbook.Charts
    
        'Your code here!
    
    Next

Hope this helps.



Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[SkipVought]

Hi,

Why not...

ActiveChart.Location Where:=xlLocationAsObject, Name:=assays(i) & controlRange

???

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[mep1]

leighmoore and skip,
After being created, the charts are moved to a seperate sheet as an object. The old code accessed the charts as a shape as shown below.

Worksheets("Summary for Export&quot.Shapes(23).IncrementLeft 265
Worksheets("Summary for Export&quot.Shapes(23).IncrementTop 2900
Worksheets("Summary for Export&quot.Shapes(22).IncrementLeft 265
Worksheets("Summary for Export&quot.Shapes(22).IncrementTop 2650

leighmoore: I am unable to navigate through the charts using your suggestion although I can see the charts in the activeworkbook in the debugger.

skip: The code you suggested results in the error
Method 'Location' of object '_Chart' failed.

If I try to change the chart name with
ActiveChart.Name = assays(i) & addString

I recieve the error
Method 'Name' of object '_Chart' failed.

 

[SkipVought]

mep,

In Excel you can have ChartObjects embedded in a Worksheet or Chart Objects as sheets. Leigh's suggestion should work.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[mep1]

Skip,
Yes, the ChartObjects are embedded in a worksheet. How do I name them so I can access them individually?
mep

 

[SkipVought]

i = 1
For Each co In ActiveSheet.ChartObjects
  co.Name = "MyChart" & i
  i = i + 1
Next

this would name all the charts in a sheet

Hope this helps

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[mep1]

Skip,
Thanks! Now I know how to access the charts embedded in a sheet.

I am trying to name the charts as they are created, before they are moved to the new sheet. When they are moved to the new sheet the name is changed. Is there a way to prevent the name from being changed when the chart is moved?

Eric