| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VBA to swap between a clustered or separated column chart
Hi,
I have some data arranged on a worksheet like this... A B 1 Name Data 2 Bob 8 3 John 12 4 Gary 10 I created the named range "ChartData" which is dynamic to allow resizing if names and data are added or removed. In the above example the range would consist of A2:B4 Next I created a chart on the spreadsheet that I want to plot the named range "ChartData" in so that it is a clustered bar chart with a legend containing the names. In VBA I use the following code... ' Activate the chart ActiveSheet.ChartObjects("Chart 1").Activate ' Add the data to the chart ActiveChart.SetSourceData Source:=Range("Sheet1!ChartData") ' Give the x axis a name ActiveChart.SeriesCollection(1).XValues = "={""Scores""}" My problem is the data always comes out as separated columns and all the same color (because they are the same series). The way to solve this is manually right click on the chart, choose select data and then swap rows/columns. The chart then becomes clustered and each name has a different colored column and appears in the legend. How can I alter my code/rearrange my data to get the clustered columns automatically? Thanks Nick |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using VBA to swap between a clustered or separated column chart
ActiveChart.SetSourceData Source:=Range("Sheet1!ChartData"), PlotBy:= _
xlColumns "Nick_F" wrote: Hi, I have some data arranged on a worksheet like this... A B 1 Name Data 2 Bob 8 3 John 12 4 Gary 10 I created the named range "ChartData" which is dynamic to allow resizing if names and data are added or removed. In the above example the range would consist of A2:B4 Next I created a chart on the spreadsheet that I want to plot the named range "ChartData" in so that it is a clustered bar chart with a legend containing the names. In VBA I use the following code... ' Activate the chart ActiveSheet.ChartObjects("Chart 1").Activate ' Add the data to the chart ActiveChart.SetSourceData Source:=Range("Sheet1!ChartData") ' Give the x axis a name ActiveChart.SeriesCollection(1).XValues = "={""Scores""}" My problem is the data always comes out as separated columns and all the same color (because they are the same series). The way to solve this is manually right click on the chart, choose select data and then swap rows/columns. The chart then becomes clustered and each name has a different colored column and appears in the legend. How can I alter my code/rearrange my data to get the clustered columns automatically? Thanks Nick |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using VBA to swap between a clustered or separated column chart
try
ActiveChart.SetSourceData Source:=Range("Sheet1!ChartData"), PlotBy:=xlRows regards r Il mio ultimo lavoro ... http://excelvba.altervista.org/blog/...ternative.html "Nick_F" wrote: Hi, I have some data arranged on a worksheet like this... A B 1 Name Data 2 Bob 8 3 John 12 4 Gary 10 I created the named range "ChartData" which is dynamic to allow resizing if names and data are added or removed. In the above example the range would consist of A2:B4 Next I created a chart on the spreadsheet that I want to plot the named range "ChartData" in so that it is a clustered bar chart with a legend containing the names. In VBA I use the following code... ' Activate the chart ActiveSheet.ChartObjects("Chart 1").Activate ' Add the data to the chart ActiveChart.SetSourceData Source:=Range("Sheet1!ChartData") ' Give the x axis a name ActiveChart.SeriesCollection(1).XValues = "={""Scores""}" My problem is the data always comes out as separated columns and all the same color (because they are the same series). The way to solve this is manually right click on the chart, choose select data and then swap rows/columns. The chart then becomes clustered and each name has a different colored column and appears in the legend. How can I alter my code/rearrange my data to get the clustered columns automatically? Thanks Nick |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using VBA to swap between a clustered or separated column chart
Somehow I suspect you want things as they are but with "vary colors by
point" Also, is that ChartData named range really dynamic. Why not make real dynamic ranges for your x-values and series values and do something like this I made these names Names: =OFFSET(Sheet1!$A$2:$A$20,,,COUNTA(Sheet1!$A$2:$A$ 20)) Data: =OFFSET(Names,,1) With ActiveChart.SeriesCollection(1) .XValues = "=Book1!Names" .Values = "=Book1!Data" End With Once you've done that you shouldn't need to do anything again to update your chart when you add new data. If you make local/sheet level names change the workbook-name to the sheet-name Regards, Peter T "Nick_F" wrote in message ... Hi, I have some data arranged on a worksheet like this... A B 1 Name Data 2 Bob 8 3 John 12 4 Gary 10 I created the named range "ChartData" which is dynamic to allow resizing if names and data are added or removed. In the above example the range would consist of A2:B4 Next I created a chart on the spreadsheet that I want to plot the named range "ChartData" in so that it is a clustered bar chart with a legend containing the names. In VBA I use the following code... ' Activate the chart ActiveSheet.ChartObjects("Chart 1").Activate ' Add the data to the chart ActiveChart.SetSourceData Source:=Range("Sheet1!ChartData") ' Give the x axis a name ActiveChart.SeriesCollection(1).XValues = "={""Scores""}" My problem is the data always comes out as separated columns and all the same color (because they are the same series). The way to solve this is manually right click on the chart, choose select data and then swap rows/columns. The chart then becomes clustered and each name has a different colored column and appears in the legend. How can I alter my code/rearrange my data to get the clustered columns automatically? Thanks Nick |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using VBA to swap between a clustered or separated column chart
On Jun 4, 7:19*pm, "Peter T" <peter_t@discussions wrote:
Somehow I suspect you want things as they are but with "vary colors by point" Thanks, all those suggestions helped. The Plotby method was not appearing when I recorded a macro and I forgot that I can use the object browser to look up methods. Cheers Nick Also, is that ChartData named range really dynamic. Why not make real dynamic ranges for your x-values and series values and do something like this I made these names Names: =OFFSET(Sheet1!$A$2:$A$20,,,COUNTA(Sheet1!$A$2:$A$ 20)) Data: =OFFSET(Names,,1) With ActiveChart.SeriesCollection(1) * * .XValues = "=Book1!Names" * * .Values = "=Book1!Data" End With Once you've done that you shouldn't need to do anything again to update your chart when you add new data. If you make local/sheet level names change the workbook-name to the sheet-name Regards, Peter T "Nick_F" wrote in message ... Hi, I have some data arranged on a worksheet like this... * *A * * * * * * * * * * B 1 *Name * * * * * * * Data 2 *Bob * * * * * * * * *8 3 *John * * * * * * * *12 4 *Gary * * * * * * * *10 I created the named range "ChartData" which is dynamic to allow resizing if names and data are added or removed. In the above example the range would consist of A2:B4 Next I created a chart on the spreadsheet that I want to plot the named range "ChartData" in so that it is a clustered bar chart with a legend containing the names. In VBA I use the following code... * *' Activate the chart * *ActiveSheet.ChartObjects("Chart 1").Activate * *' Add the data to the chart * *ActiveChart.SetSourceData Source:=Range("Sheet1!ChartData") * *' Give the x axis a name * *ActiveChart.SeriesCollection(1).XValues = "={""Scores""}" My problem is the data always comes out as separated columns and all the same color (because they are the same series). The way to solve this is manually right click on the chart, choose select data and then swap rows/columns. The chart then becomes clustered and each name has a different colored column and appears in the legend. How can I alter my code/rearrange my data to get the clustered columns automatically? Thanks Nick |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using VBA to swap between a clustered or separated column chart
On Jun 4, 7:19*pm, "Peter T" <peter_t@discussions wrote:
Somehow I suspect you want things as they are but with "vary colors by point" Thanks, all those suggestions helped. The Plotby method was not appearing when I recorded a macro and I forgot that I can use the object browser to look up methods. Cheers Nick Also, is that ChartData named range really dynamic. Why not make real dynamic ranges for your x-values and series values and do something like this I made these names Names: =OFFSET(Sheet1!$A$2:$A$20,,,COUNTA(Sheet1!$A$2:$A$ 20)) Data: =OFFSET(Names,,1) With ActiveChart.SeriesCollection(1) * * .XValues = "=Book1!Names" * * .Values = "=Book1!Data" End With Once you've done that you shouldn't need to do anything again to update your chart when you add new data. If you make local/sheet level names change the workbook-name to the sheet-name Regards, Peter T "Nick_F" wrote in message ... Hi, I have some data arranged on a worksheet like this... * *A * * * * * * * * * * B 1 *Name * * * * * * * Data 2 *Bob * * * * * * * * *8 3 *John * * * * * * * *12 4 *Gary * * * * * * * *10 I created the named range "ChartData" which is dynamic to allow resizing if names and data are added or removed. In the above example the range would consist of A2:B4 Next I created a chart on the spreadsheet that I want to plot the named range "ChartData" in so that it is a clustered bar chart with a legend containing the names. In VBA I use the following code... * *' Activate the chart * *ActiveSheet.ChartObjects("Chart 1").Activate * *' Add the data to the chart * *ActiveChart.SetSourceData Source:=Range("Sheet1!ChartData") * *' Give the x axis a name * *ActiveChart.SeriesCollection(1).XValues = "={""Scores""}" My problem is the data always comes out as separated columns and all the same color (because they are the same series). The way to solve this is manually right click on the chart, choose select data and then swap rows/columns. The chart then becomes clustered and each name has a different colored column and appears in the legend. How can I alter my code/rearrange my data to get the clustered columns automatically? Thanks Nick |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using VBA to swap between a clustered or separated column chart
ActiveChart.SetSourceData Source:=Range("Sheet1!ChartData"), PlotBy:= _
xlColumns "Nick_F" wrote: Hi, I have some data arranged on a worksheet like this... A B 1 Name Data 2 Bob 8 3 John 12 4 Gary 10 I created the named range "ChartData" which is dynamic to allow resizing if names and data are added or removed. In the above example the range would consist of A2:B4 Next I created a chart on the spreadsheet that I want to plot the named range "ChartData" in so that it is a clustered bar chart with a legend containing the names. In VBA I use the following code... ' Activate the chart ActiveSheet.ChartObjects("Chart 1").Activate ' Add the data to the chart ActiveChart.SetSourceData Source:=Range("Sheet1!ChartData") ' Give the x axis a name ActiveChart.SeriesCollection(1).XValues = "={""Scores""}" My problem is the data always comes out as separated columns and all the same color (because they are the same series). The way to solve this is manually right click on the chart, choose select data and then swap rows/columns. The chart then becomes clustered and each name has a different colored column and appears in the legend. How can I alter my code/rearrange my data to get the clustered columns automatically? Thanks Nick |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using VBA to swap between a clustered or separated column chart
Somehow I suspect you want things as they are but with "vary colors by
point" Also, is that ChartData named range really dynamic. Why not make real dynamic ranges for your x-values and series values and do something like this I made these names Names: =OFFSET(Sheet1!$A$2:$A$20,,,COUNTA(Sheet1!$A$2:$A$ 20)) Data: =OFFSET(Names,,1) With ActiveChart.SeriesCollection(1) .XValues = "=Book1!Names" .Values = "=Book1!Data" End With Once you've done that you shouldn't need to do anything again to update your chart when you add new data. If you make local/sheet level names change the workbook-name to the sheet-name Regards, Peter T "Nick_F" wrote in message ... Hi, I have some data arranged on a worksheet like this... A B 1 Name Data 2 Bob 8 3 John 12 4 Gary 10 I created the named range "ChartData" which is dynamic to allow resizing if names and data are added or removed. In the above example the range would consist of A2:B4 Next I created a chart on the spreadsheet that I want to plot the named range "ChartData" in so that it is a clustered bar chart with a legend containing the names. In VBA I use the following code... ' Activate the chart ActiveSheet.ChartObjects("Chart 1").Activate ' Add the data to the chart ActiveChart.SetSourceData Source:=Range("Sheet1!ChartData") ' Give the x axis a name ActiveChart.SeriesCollection(1).XValues = "={""Scores""}" My problem is the data always comes out as separated columns and all the same color (because they are the same series). The way to solve this is manually right click on the chart, choose select data and then swap rows/columns. The chart then becomes clustered and each name has a different colored column and appears in the legend. How can I alter my code/rearrange my data to get the clustered columns automatically? Thanks Nick |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using VBA to swap between a clustered or separated column chart
try
ActiveChart.SetSourceData Source:=Range("Sheet1!ChartData"), PlotBy:=xlRows regards r Il mio ultimo lavoro ... http://excelvba.altervista.org/blog/...ternative.html "Nick_F" wrote: Hi, I have some data arranged on a worksheet like this... A B 1 Name Data 2 Bob 8 3 John 12 4 Gary 10 I created the named range "ChartData" which is dynamic to allow resizing if names and data are added or removed. In the above example the range would consist of A2:B4 Next I created a chart on the spreadsheet that I want to plot the named range "ChartData" in so that it is a clustered bar chart with a legend containing the names. In VBA I use the following code... ' Activate the chart ActiveSheet.ChartObjects("Chart 1").Activate ' Add the data to the chart ActiveChart.SetSourceData Source:=Range("Sheet1!ChartData") ' Give the x axis a name ActiveChart.SeriesCollection(1).XValues = "={""Scores""}" My problem is the data always comes out as separated columns and all the same color (because they are the same series). The way to solve this is manually right click on the chart, choose select data and then swap rows/columns. The chart then becomes clustered and each name has a different colored column and appears in the legend. How can I alter my code/rearrange my data to get the clustered columns automatically? Thanks Nick |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Clustered and Stacked Column Chart | Excel Discussion (Misc queries) | |||
| Creating a clustered column chart | Charts and Charting in Excel | |||
| 2d-Column (Clustered Column) chart with Standard Deviation Bars | Charts and Charting in Excel | |||
| Needed: Chart that combines clustered column and stacked column types | Charts and Charting in Excel | |||
| combination clustered column and stacked bar chart | Charts and Charting in Excel | |||
Hybrid Mode
