1. Home
  2. >>
  3. legend
  4. >>
  5. Change the orientation of the chart and legend using excel VBA

Change the orientation of the chart and legend using excel VBA



Change the orientation of the chart and legend using excel VBA
legend

Change the orientation of the chart and legend using excel VBA


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

Could you show me with VBA how I can orient this chart as a chart (2) ?

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

GRAPHICS 1 STARTI EXCLUSIVELY LOOKING LIKE THIS

GRAPH 2 THIS IS HOW I DO MANUAL

(reoriented legend entries – left and category labels – right)

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

GRAPH 4 The final graph looks like this, this is what I want to get in VBA

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

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

Your columns and rows are reversed. When you have selected the chart and go to the “Design” contextual tab and click on “Switch Row / Column”. Does it solve it? If yes you can use: 

  ActiveChart.PlotBy = xlRows or ActiveChart.PlotBy = xlColumns

to do it programmatically.