Chart DataSource Change

I am trying to change the datasource of an existing chart
in VBA. Is there a way to do this in VBA. Our isssue is that we are moving to
another source.

So the example would be we take a range from an existing worksheet and move
this to a new worksheet with the chart, but not in the same range. So the
variables would be that we need to adjust the source range be the difference
in the row or columns for each of the series. The main issue is how do we get
the data source of the existing chart series?

Thanks for any help.

JJ,

You can use macros to automate a lot of the series changes you want . . .

The first macro below will return the series color index for series number
1. If you don’t know the color index for the series that you want, try a
color and then run this. You can then apply the number to the second and
third macros below.

If you want the marker colors, take out the apostrophe in front of those
lines of code:

Sub SeriesColorIndex()
‘get color indices
Dim A As Integer 'Line Color Index
Dim B As Integer 'Marker Background Color Index
Dim C As Integer 'Marker Foreground Color Index
Set Cht = ActiveChart
Set Srs1 = Cht.SeriesCollection(1)
A = Srs1.Border.ColorIndex
‘B = Srs1.MarkerBackgroundColorIndex
‘C = Srs1.MarkerForegroundColorIndex
MsgBox A
MsgBox B
MsgBox C
End Sub

This macro colors all of the series the same color:

Sub ColorAllSeries()
Set Cht = ActiveChart
Set Srs = Cht.SeriesCollection(2)
For Each Sr In Srs
Sr.Border.ColorIndex = 6
‘Sr.MarkerBackgroundColorIndex = 6
‘Sr.MarkerForegroundColorIndex = 6
Next Sr
End Sub

This macro colors the series number 2:

Sub ColorSingleSeries()
Set Cht = ActiveChart
Set Srs = Cht.SeriesCollection(2)
Srs.Border.ColorIndex = 6
‘Srs.MarkerBackgroundColorIndex = 6
‘Srs.MarkerForegroundColorIndex = 6
End Sub

To create a custom chart that you base all of your other on (so you don’t
have to format each line in each new chart that you create), click on your
master chart and go through the following:

Chart -> Chart Type -> Custom Types Tab
Go to Select From -> User Defined
Hit “Add�
Give the chart a name and description
Save the chart and use it as the default for your others

----
Regards,
John Mansfield
http://www.pdbook.com


"jj" wrote:

> I am trying to change the datasource of an existing chart
> in VBA. Is there a way to do this in VBA. Our isssue is that we are moving to
> another source.
>
> So the example would be we take a range from an existing worksheet and move
> this to a new worksheet with the chart, but not in the same range. So the
> variables would be that we need to adjust the source range be the difference
> in the row or columns for each of the series. The main issue is how do we get
> the data source of the existing chart series?
>
> Thanks for any help.
>

jj,

I apologize, I originally wanted to say that you can use this code to move
the data source for the chart (my original post was meant for another
question).

Sub ChangeSourceRange()
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("E2:F5")
End Sub

Set the source range to the reference in the code. Activate the chart by
clicking on it and run the macro. Hope this helps - sorry about that.

----
Regards,
John Mansfield
http://www.pdbook.com


"jj" wrote:

> I am trying to change the datasource of an existing chart
> in VBA. Is there a way to do this in VBA. Our isssue is that we are moving to
> another source.
>
> So the example would be we take a range from an existing worksheet and move
> this to a new worksheet with the chart, but not in the same range. So the
> variables would be that we need to adjust the source range be the difference
> in the row or columns for each of the series. The main issue is how do we get
> the data source of the existing chart series?
>
> Thanks for any help.
>

You cannot get the source data range of the entire chart. Sometimes when you open
the Source Data dialog you see it in the Data Range tab, sometimes you don't, but
Excel never shares this information with VBA.

You can get the data range for a single series by parsing the series formula. That's
a real pain, so we're all very grateful to John Walkenbach for writing a class
module to do this for us, and sharing it on his web site:

http://www.j-walk.com/ss/excel/tips/tip83.htm

Your objective seems rather daunting, to say the least.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

jj wrote:

> I am trying to change the datasource of an existing chart
> in VBA. Is there a way to do this in VBA. Our isssue is that we are moving to
> another source.
>
> So the example would be we take a range from an existing worksheet and move
> this to a new worksheet with the chart, but not in the same range. So the
> variables would be that we need to adjust the source range be the difference
> in the row or columns for each of the series. The main issue is how do we get
> the data source of the existing chart series?
>
> Thanks for any help.
>