Macro code to turn a graph from linked to a series - RESOLVED

[strobinson1]

If you have a graph that is linked to a specific data range,

if you highlight the series on the graph, click in the formula bar, and hit F9....

The series changes from being references to a set of cells to the actual values. a bit like a paste special...values for a graph.


Does anyone know a way of coding this procedure into a macro ??????????

[disruptivehair]

If you have a graph that is linked to a specific data range,

if you highlight the series on the graph, click in the formula bar, and hit F9....

The series changes from being references to a set of cells to the actual values. a bit like a paste special...values for a graph.


Does anyone know a way of coding this procedure into a macro ??????????

Go to Tools --> Macro --> Record New Macro, record yourself performing that action, and adapt the code to fit your situation.

[strobinson1]

yeah I tried that but found that it doesn't actually record the process...hence my issue !!!

............

[disruptivehair]

yeah I tried that but found that it doesn't actually record the process...hence my issue !!!

............

Strange. Well, generally to change the chart type of a series you go worksheet.chartobjects(ordinal number of chart).chart.seriescollection(number of the series you're changing).charttype = <type of chart you want to change to>

For example, I have an Excel worksheet called xlSheet. I want to change series 1 to a clustered column chart type. This works:

xlSheet.ChartObjects(1).Chart.SeriesCollection(1).ChartType = xlColumnClustered

This site has some good info on decoding Excel's mnemonics for chart types.

http://tushar-mehta.server101.com/ex..._mnemonics.htm

Good luck.

[strobinson1]

I'll have a look at that site cheers....

however I don't think I explained myself properly so I'll try again.....

If you create a simple graph using this data (ie click the chart wizard and just 'Finish') you get a simple bar chart.


Jan 2
Feb 4
Mar 4
Apr 8
May 6
Jun 7
Jul 1
Aug 5
Sep 4
Oct 6
Nov 8
Dec 2

If you click on the bars on the chart, the formula bar will show you something like:

=SERIES(,Sheet1!$A$2:$A$13,Sheet1!$B$2:$B$13,1)

showing the cells references for the data.

Now...if you click in the formula bar as if you were going to edit this and then press 'F9', it will change into:

=SERIES(,{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},{2,4,4,8,6,7,1,5, 4,6,8,2},1)


ie. No longer cell references but hardcoded data. Very useful if you want to export a chart without it's workings etc....

But I am trying to achieve this with some macro automation.......


Any ideas????

[disruptivehair]

I'll have a look at that site cheers....

however I don't think I explained myself properly so I'll try again.....

If you create a simple graph using this data (ie click the chart wizard and just 'Finish') you get a simple bar chart.


Jan 2
Feb 4
Mar 4
Apr 8
May 6
Jun 7
Jul 1
Aug 5
Sep 4
Oct 6
Nov 8
Dec 2

If you click on the bars on the chart, the formula bar will show you something like:

=SERIES(,Sheet1!$A$2:$A$13,Sheet1!$B$2:$B$13,1)

showing the cells references for the data.

Now...if you click in the formula bar as if you were going to edit this and then press 'F9', it will change into:

=SERIES(,{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},{2,4,4,8,6,7,1,5, 4,6,8,2},1)


ie. No longer cell references but hardcoded data. Very useful if you want to export a chart without it's workings etc....

But I am trying to achieve this with some macro automation.......


Any ideas????

Ummmmm I'm not sure what you're trying to do here.

[strobinson1]

Have you tried to follow what I did ?

[disruptivehair]

Have you tried to follow what I did ?

Basically you want to be able to save that string that appears in the Formula bar after you press F9, yes? I don't know how to do that, I'm sorry.

[strobinson1]

nope....

I want to perform the process that changes the formula into the string.

Thanks for trying anyway....


Any other ideas anyone ! ???

[strobinson1]

FINALLY ! I have found the solution to this !

http://peltiertech.com/Excel/ChartsH...ChartData.html