Change orientation of chart and legend using excel VBA

0
votes

For some reason if there is less than four rows of data and you try and scatter chart it the cluster (column A) is shown on the legend, but if more than 4 rows of data exist then the other columns (A, B, C) are on the legend (Y axis).

Can you please show me using VBA how do I re orientate this graph to look like graph (2)?

I need to reorient the legend series to be on the horizontal category axis using VBA(since the macro recorder doesn't show how to do this.

GRAPH 1 STARTS OFF LOOKING LIKE THIS enter image description here

GRAPH 2 THIS IS HOW I DO IT MANUALLY

(reorient legend entries - left, and category labels - right) enter image description here

GRAPH 3 The scatterchart dialog ends up looking like this, which is what I want to do in VBA enter image description here

GRAPH 4 Final Graph looks like this, This is what I want to achieve in VBA enter image description here

Lastly this is what the macro recorder creates, but when I try and run it, its missing, the range and Plotby parameters and fails

Range("A1:D3").Select
Range("D3").Activate
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData
1
excelvbacharts

1 Answers

2
votes

Your columns and rows are reversed. When you have the chart selected go und "Design" contextual tab and click "Switch Row/Column". Does that fix it? If yes you can use:

 ActiveChart.PlotBy = xlRows or  ActiveChart.PlotBy = xlColumns

to do it programatically.