Macro code to turn a graph from linked to a series - RESOLVED
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 ??????????
Re: Macro code to turn a graph from linked to a series
Quote:
Originally Posted by 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 ??????????
Go to Tools --> Macro --> Record New Macro, record yourself performing that action, and adapt the code to fit your situation. :thumb:
Re: Macro code to turn a graph from linked to a series
yeah I tried that but found that it doesn't actually record the process...hence my issue !!!
............
Re: Macro code to turn a graph from linked to a series
Quote:
Originally Posted by strobinson1
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:
VB Code:
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.
Re: Macro code to turn a graph from linked to a series
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????
Re: Macro code to turn a graph from linked to a series
Quote:
Originally Posted by 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????
Ummmmm I'm not sure what you're trying to do here. :confused:
Re: Macro code to turn a graph from linked to a series
Have you tried to follow what I did ?
Re: Macro code to turn a graph from linked to a series
Quote:
Originally Posted by strobinson1
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.
Re: Macro code to turn a graph from linked to a series
nope....
I want to perform the process that changes the formula into the string.
Thanks for trying anyway.... :)
Any other ideas anyone ! ???
Re: Macro code to turn a graph from linked to a series
