How do I modify charts thorugh VB

[Rayo K]

I have a spreadsheet with ~120 sheets each with one chart. I recently
modified the chart and copied the new chart to each sheet using VB to copy.
unfortunately, I realized that the sourcedata is the same sheet for all
charts. I now want to change the source data for each sheet to the range
below.

This code executes but when i check the charts, the sourcedata still refers
to the 1st sheet. What am I doing wrong?

For each ws1 in Worksheets

Set ch1 = ws1.ChartObjects.Item(1).Chart
ch1.SetSourceData Source:=Sheets(ws1.Name).Range("E84:F85"), PlotBy _
:=xlColumns

 

[Jon Peltier]

This worked for me (I changed the range to match my example):

Sub ChangeSourceSheet()

Dim ws1 As Worksheet
Dim ch1 As Chart

For Each ws1 In Worksheets

Set ch1 = ws1.ChartObjects.Item(1).Chart
ch1.SetSourceData Source:=Sheets(ws1.Name).Range("A4:B20"), PlotBy _
:=xlColumns

Next

End Sub

Are you sure you don't need to change ChartObjects(2)?

- Jon

 

[Rayo K]

That's a good question. I don't understnad the embedded chart object model.
How would I know which chartobject corresponds to my chart? I chose
chartobjects(1) because I only have one chart. But I have no way of knowing
whether my chart has a different index number, at least not as far as I can
tell.

At any rate, the code you used isn't working for me. Maybe my chart has some
other index number, but how can I know what it is?

 

[Jon Peltier]

If you have one chart, then it is ChartObjects(1). You can see a chart
object's name by holding shift while clicking on the chart, then looking in
the name box (above the row numbers).

To list all of the charts, go to the VB Editor and open the Immediate
Window. Paste this line, click the cursor back into the line, and press
enter:

for i = 1 to activesheet.chartobjects.count : ? i,
activesheet.chartobjects(i).name : next

this gives you a numerical list of the chart objects in the sheet along with
their names.

You can get the active chart's name by using this line in the Immediate
Window:

? activechart.parent.name

Has the series formula of the chart been updated to reflect the new source
data sheet?

- Jon

 

[Rayo K]

It still does not work. The sourcedata remains unchanged. When I tried the
code for hte immediate window, it gave an error saying there was a "Next
without for" error.

When I tried entering "activesheet.chartobjects.count" it said the count
method failed.

 

[Jon Peltier]

Something's wrong. Even if there are no charts, ChartObjects.Count should
return zero.

- Jon

 

[Peter T]

It still does not work. The sourcedata remains unchanged. When I tried the

code for hte immediate window, it gave an error saying there was a "Next
without for" error.

Did you unwrap the two lines bfore pressing enter.

When I tried entering "activesheet.chartobjects.count" it said the count
method failed.

Did you preceed with a ?

(I havn't looked at your original problem)

Regards,
Peter T

 

[Rayo K]

OK,

I tried "msgbox sheets("17th-3").chartobjects.count" in the immediate
window, and it showed "2"

The problem is, there is only one chart on that sheet. I tried another one
and it showed 6. I think there were probably old charts that were cut or
deleted that are still there somehow.

Is there a way to clear out the old charts, and if not, how can I identify
which chart is still on the sheet?

 

[Jon Peltier]

In Excel 2003 and maybe 2002, there's a button you can add to the Drawing
Toolbar. Click the arrow thingie on the right edge the Drawing toolbar,
click Add or Remove Buttons, Drawing, and choose Select Multiple Items.
Click on the new button that appears on the Drawing toolbar, and it will
give you a list of all the objects in the sheet.

When you delete columns or rows from behind a chart, the chart gets thinner,
becoming a one-pixel wide chart lined up along cell boundaries or along the
top or left edge of the sheet, where not even Superman could see it.

- Jon

 

[Ron5440]

To determine the name of the active chart, select it and click 'view' on the
toolbar and select 'Chart Window'.