cannot set plotarea size

Originally Posted by Nicky

Hi
It's a mixed bar/line chart embedded on a worksheet (I have no problem with using this code on chart sheets). Either the size doesn't change, or sometimes it changes to a different size than I specify, but often it adjusts the plotarea height to the largest that will fit the chartarea (about 280 points, when I want it to be 240 high).

Have you tried recording a Macro and manually setting the size to see what happens? - is there a hieght-width ratio setting with no room to adjust width?

---

Bryan, yes, I can change the height manually, there doesn't seem to be a fixed proportion of height to width. But when I record the action of setting the height and play it back, it doesn't work.

Originally Posted by Nicky

Bryan, yes, I can change the height manually, there doesn't seem to be a fixed proportion of height to width. But when I record the action of setting the height and play it back, it doesn't work.

Hi,


re-tested, and it should. Did you try the macro immediately after recording (and resizing) ?

Could this be an old '95 or '97 book updated to the current version?

Do other chart option 'set' correctly in this area of code, ie
ActiveChart.ChartType = xlPie
or
ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).AxisGroup = 1
etc

any more clues?
---

Bryan

Thank you for taking the time to help with this.

They are mostly mixed area or bar and line charts that don't conform exactly to Excel standard charts, which may be something to do with it.

I have also discovered that it seems in some way related to the placement of the x-axis chart title. If I go into a chart and manually resize the plot area, and then move the chart title – perhaps so it’s no longer in a fixed relationship with the bottom of the plotarea – I can then run a macro to resize that sort of works, although weirdly it adds to the plotarea height I’m trying to set, so

activechart.plotarea.Height = 190

sets the height at exactly 214 points.

So you’re probably right about Excel trying to retain some fixed relationship with other chart elements, but I can’t work out how these are determined or what they are.

Originally Posted by Nicky

Bryan

Thank you for taking the time to help with this.

They are mostly mixed area or bar and line charts that don't conform exactly to Excel standard charts, which may be something to do with it.

I have also discovered that it seems in some way related to the placement of the x-axis chart title. If I go into a chart and manually resize the plot area, and then move the chart title – perhaps so it’s no longer in a fixed relationship with the bottom of the plotarea – I can then run a macro to resize that sort of works, although weirdly it adds to the plotarea height I’m trying to set, so

activechart.plotarea.Height = 190

sets the height at exactly 214 points.

So you’re probably right about Excel trying to retain some fixed relationship with other chart elements, but I can’t work out how these are determined or what they are.

the problem is that a number of items affect the 'plot' size, Chart size, labels, 3 * Axis labels (for secondary series) and Legend being the major influence and there's not (to my limited knowledge) a lot of information about the calculations. Any Pope shows some calculations for Plot, but that's to decide which of 9 Pie charts gets what amount of area allowed, and that's hardly useful here.

Good luck with this, and if you have a copy of the chart that is 'data modified' to be postable you could post it here as a .zip
(ie, copy the sheet to a new book, copy & Paste Special = Values the charts input data, then remove any figures and formula not directly related, and modify the numbers if required to protect your data.)

---

Bryan

now I know that Excel's trying to keep the components in relationship I've worked out a macro that moves the axis labels and legend before adjusting the plotarea height, then moves them back again, and this sort of works (though I still have to understate my desired plotarea height by about 20 points to get the required outcome). Happily, there are only about 60 charts in this workbook, so I could get through them fairly easily with this slightly messy workaround.

Thanks again for your help on this.

Originally Posted by Nicky

Bryan

now I know that Excel's trying to keep the components in relationship I've worked out a macro that moves the axis labels and legend before adjusting the plotarea height, then moves them back again, and this sort of works (though I still have to understate my desired plotarea height by about 20 points to get the required outcome). Happily, there are only about 60 charts in this workbook, so I could get through them fairly easily with this slightly messy workaround.

Thanks again for your help on this.

OK - good to see a workaround, and thanks for your response.
---