Excel Chart Plot Area Auto Size Challenge 1

[TopJack]

In Excel 2003 I'm creating a very simple area chart with a legend placed to the right of the plot area. If I move the legend to say bottom of the plot area (right mouse click and "Format Legend/Placement/Bottom") the plot area itself automatically resizes to maximise the visibility. This is good news for me.

The problem occurs when the plot area has been manually resized (using the mouse). When the legend is now moved it only resizes the plot area if it overlaps with the legend. This is bad news for me because it doesn't maximise the possible size for the plot area.

If a plot area is manually altered it somehow turns off the ability for the plot area to be auto sized (as if it was a new chart). How do I programmatically turn this back on again ?

I know how to calculate a good plot area after the chart has been manually resized, ie activechart.plotarea.height = pixels, but I was wondering if there was an easier option because a new chart already does the calculation for me.

I'm looking for something like :- activechart.plotarea.autosize = true.

This is your challenge if you choose to accept !

 

[SkipVought]

TJ,

Interesting. This is one of seveveral anomylies in the Chart object, it seems. Sure looks as if the PlotArea Width property is STUCK after you manually chage it.

I'd just...

    With ActiveChart
        With .PlotArea
            .Width = ActiveChart.ChartArea.Width - .Left
        End With
    End With

Skip,
[sub]
When a wee mystic is on the loose..
It's a Small Medium at Large! [/sub]

 

[TopJack]

I thought that might be the answer. Another feature of Excel !

Thanks for your time Skip.

 

[TopJack]

Never one to give up easily. With some usefull help from Jon Peltier (check out his website - its got good content). He has discovered that in terms of Excel menus you can select "Chart/Chart Type/Standard Types" and check "Default Formatting" and this will force the chart back to allowing the plot area to resize automatically again.

If you want to retain your original custom chart formatting you can create a user defined chart type by "Chart/Chart Type/Custom Types" check "User Defined" and "Add" your own chart type (this must be all done before any manual adjustments are made to the plot area in particular). Then you could apply a similar statement to this to bring back the auto resizing plot area after a manual adjustment :-

ActiveChart.ApplyCustomType ChartType:=xlUserDefined, TypeName:="My Chart Formats"

I guess these types of 'features' are sent to test us.

 

[SkipVought]

Thanks for digging for an answer and posting your findings. ==>*

Skip,
[sub]
When a wee mystic is on the loose..
It's a Small Medium at Large! [/sub]