Using VBA to swap between a clustered or separated column chart

N

Nick_F

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
 
P

Peter T

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
 
N

Nick_F

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top