Resize chart area without resizing plot area

[Janwillem van Dijk]

Hi,
With the help of your answers on my question of July 30 and some digging
in excel4 I succeeded in making the inside of the plot area exactly a
square with predefined dimensions.

I made a function that finds the dimensions of a rectangle that encloses
all elements in the plot including title, axes-titles and text boxes.

Now I want the chart area to fit tightly around all the actual plot
elements. But I do not succeed in resizing the chart area without
resizing the plot.

Is there some (hidden) feature that switches off autoresizing like there
is one for the font?

When I have a solution I will publish the final results of my endeavours
on this newsgroup of course.

Tanks,
Janwillem

[Peter T]

Hi Janwillem,

> With the help of your answers on my question of July 30 and some digging
> in excel4 I succeeded in making the inside of the plot area exactly a
> square with predefined dimensions.

I'll be interested to see. Not straightforward, charts have something of a
free spirit about them.

> I made a function that finds the dimensions of a rectangle that encloses
> all elements in the plot including title, axes-titles and text boxes.

So you are saying there's nothing between the plot-area and the outside of
the chart. The plot area of course has two parts to it, the graph area and
an area outside for axis lables etc. Though I'm sure you're very well aware
of that.

> Now I want the chart area to fit tightly around all the actual plot
> elements. But I do not succeed in resizing the chart area without
> resizing the plot.

Could you size the chart area as required first, then size the plot area to
slightly less than the chart area. Actually that would mean sizing the
chartobject slightly larger than required, even if it doesn't have a border.

> Is there some (hidden) feature that switches off autoresizing like there
> is one for the font?

AFAIK - no, though I'd be happy to learn otherwise. There is autoscaling for
3D chart types but I don't think that will help you.

Regards,
Peter T

[Jon Peltier]

> With the help of your answers on my question of July 30 and some digging
> in excel4 I succeeded in making the inside of the plot area exactly a
> square with predefined dimensions.
>
> I made a function that finds the dimensions of a rectangle that encloses
> all elements in the plot including title, axes-titles and text boxes.
>
> Now I want the chart area to fit tightly around all the actual plot
> elements. But I do not succeed in resizing the chart area without
> resizing the plot.

Why not just make the chart area sufficiently large to always encompass
the axis elements? Otherwise you will have to endlessly tweak the chart
area, the plot area, and other elements. You can make the chart area
invisible (no border, no fill) if you want the charts closer together
without apparent overlap.

> Is there some (hidden) feature that switches off autoresizing like there
> is one for the font?

No.

> When I have a solution I will publish the final results of my endeavours
> on this newsgroup of course.

If you are very patient, you could do this with an iterative VBA
approach. The problem is that it is never going to be perfect, and
sometimes it will be off by a pixel, no matter how loudly you admonish
Excel to behave.

One trick is to hide all of the axis ticks and labels, so that the plot
area isn't much larger than the inside area, and so that the margin
between plot area and inside area does not have to adjust for label
format changes, etc. Use the arbitrary axis scale trick from my web site
for your labels, which lets the labels go outside the plot area without
stretching the inside area. See this page for details:

http://peltiertech.com/Excel/Charts/ArbitraryAxis.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______