Posted to microsoft.public.excel.programming
|
|
How do I modify charts thorugh VB
To determine the name of the active chart, select it and click 'view' on the
toolbar and select 'Chart Window'.
"Peter T" wrote:
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" wrote in message
...
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" wrote:
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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______
"Rayo K" wrote in message
...
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" wrote:
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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______
"Rayo K" wrote in message
...
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
|