Reinstate Excel chart default resizing behaviour using VBA

Question

I am looking for a way to reinstate the default/native resizing behaviour of a chart in Excel 2010 once it has been disabled (e.g. by manipulating the chart with VBA).

Now I haven't been able to find anything anywhere about the behaviour I have in mind, so I am going to assume that it needs detailed explanation.

Input and select random numerical data into 4-5 cells in Excel, and insert a new Clustered Columns chart. You need to see a the chart's plot area. Now select the chart, and get the PlotArea.Top value with the following line

ActiveChart.PlotArea.Top

If you haven't touched the chart, this should return a value of 7. Now use one of the chart's handlebars to resize the chart vertically, and use the same command line again.

activechart.plotarea.top

Notice how the value returned is still 7. Now set this property to 7 in VBA.

ActiveChart.PlotArea.Top = 7

Again, grab one of the handlebars, resize the chart vertically and get the .top property again using:

ActiveChart.PlotArea.Top

Notice how the value has now changed. It will be either smaller or greater than 7 depending on whetehr you decreased or increased the size of the chart.

Once any element of a chart has been moved either manually or with VBA code, it loses this "absolute position" property and begins moving inside the ChartArea whenever the chart is resized. While some elements can be reset using .SetElement, this does not work for the Plot Area. For example, the following command lines do not reinstate the behaviour I am describing.

ActiveChart.SetElement msoElementPlotAreaNone
ActiveChart.SetElement msoElementPlotAreaShow

I do a lot of automated resizing of charts with VBA, and having the plot area move around by itself makes it a lot harder to predict the effect of resizing the chart and leads to inconstant results.

So back to the question: does anyone know of a way to reinstate this default behaviour, either for the entire chart, or at least specifically for the PlotArea?

Thanks in advance to anyone who may help!

Vincent

Thanks for the suggestion! Unfortunately it doesn't work in my situation (eg. For the Plot Area) — Vincent Courtemanche
– Vincent Courtemanche, Commented Jan 29, 2015 at 16:56

ClearBlueSky85 · Accepted Answer · 2016-05-02 17:59:07Z

2

I ran into this when I manually resized the plot area and then when the legend is moved it did not resize the plot area at all.

I had tried to save my chart as a template (right click save as template in Excel 2013) but this still had the plot area manually set.

Therefore I would recommend keeping the auto-size behavior before saving a template, since the only way I know to re-set the chart auto-sizing behavior after it has been manually modified is to use a macro

Here is the macro I used to reinstate the auto-sizing behavior

Sub Macro1()
'
  ' this selects the chart based on the chart name
  ActiveSheet.ChartObjects("Chart 4").Activate
  ' this selects the plot area
  ActiveChart.PlotArea.Select
  ' this clears any custom formatting such as borders or fill colors
  ActiveChart.PlotArea.ClearFormats
  ' this resets the auto-sizing behavior after plot area manually re-sized
  ActiveChart.PlotArea.Position = xlChartElementPositionAutomatic
End Sub

References

answered May 2, 2016 at 17:59

ClearBlueSky85

4836 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vincent Courtemanche Over a year ago

wow, can't believe someone finally figured this out :) after a little bit of testing the xlChartElementPositionAutomatic attribute looks exactly like what I was looking for. Thanks!

Collectives™ on Stack Overflow

Reinstate Excel chart default resizing behaviour using VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related