Chart Legend placement

[roc_on_the_rocks]

I’m facing an unexpected problem positioning the legend on a scatter plot chart (Excel 2007).

Once the chart is plotted (through VBA), the left side of the chart legend overlaps with secondary Y-axi labels. So I’ve been trying to move the chart legend further to the right, against the chart area border.

My surprise is that when moving the chart legend with VBA code, I cannot get it closer than about 3/8 inch between the legend and chart border. But if I manually move it, I can get it touching the right-side area border.

    ActiveChart.Legend.Left = 500
    ActiveChart.Legend.Width = 150

An alternative to resolve the overlapping issue is to make the actual plot area smaller (narrower), but I’m avoiding that because the smaller plot area makes is harder to read it.

Any help will be greatly appreciated. Thanks in advance!

 

[Mungyo]

Have you tried

  ActiveChart.Legend.Position = xlLegendPositionRight
  ActiveChart.Legend.Left = ActiveChart.ChartArea.Width

 

[roc_on_the_rocks]

Thanks Mungyo.

Your code:

ActiveChart.Legend.Position = xlLegendPositionRight
ActiveChart.Legend.Left = ActiveChart.ChartArea.Width

... does place the chart legend at the desired location: against the right-side area border.

However, the legend moves away from the area border if I define legend width narrower than 177.94 (which is the width if I don't state it).

The ActiveChart.Legend.Width and ActiveChart.Legend.Left are overriding each other, depending on the order they are stated on the VBA code:

If I first define Legend Position (to ActiveChart.ChartArea.Width), and then Legend Width (to 160), the Width 'wins' and chart legend moves away from the border.
​

If I first define Legend Width (to 160), and then Legend Position (to ActiveChart.ChartArea.Width), the Position 'wins' and the Width is overridden (becomes 177.94).
​

Any idea on how to resolve it?

 

[Mungyo]

Hmm. Puzzling. How about :

ActiveChart.Legend.Position = xlLegendPositionRight
ActiveChart.Legend.Width = 160
ActiveChart.Legend.Left = ActiveChart.ChartArea.Width

 

[roc_on_the_rocks]

Awesome Mongyo! You resolved it!!

Thanks a million!