Resizing Dimensions of Chart in Chart Sheet

Hello,

I have a large number of charts, each in their own tab / chart sheet. I need the dimension of each chart to be 22.5 cm wide by 15.0 cm tall. As I understand it, the dimension of a chart in a chart sheet can't be directly changed. Instead, you have the change the dimensions of the margins.

Question: Do you know how large the margins should be in order for the chart to be 22.5 cm X 15.0 cm? Please see the (????) in the code below. I've tried all sorts of experiments and calculations and can't figure it out.

Please Login or Register  to view this content.

Thank you very much for your help.

As I understand it, the dimension of a chart in a chart sheet can't be directly changed. Instead, you have the change the dimensions of the margins.

Is this new in newer versions of Excel, because I recall changing the size of the chart area and plot area independent of each other (and independent of margins) before.

Here's a test procedure I just put together:

1) I started with a new, blank workbook.
1a) Entered a dozen or so =RAND() functions in two columns for some random x,y points for a scatter plot.
1b) Insert -> scatter chart -> move to chart sheet.
2) Test procedure

Please Login or Register  to view this content.

Run this procedure, and it enters debug mode at the stop statement. Then step through the function (or run to the next stop), and see what is happening in the 4 variables. The first block With simply tests to see if I can read the height and width properties of the chart area and the plot area separately. Then I assign new values (in points -- at this point, I wasn't worried about an exact conversion from 22 cm to points) to these variables, and the second block With attempts to assign these values to the height and width properties of chart area and plot area independently. When I ran this, I could change the size of each area independently. What do you get from this procedure?

Hi MrShorty,

Thanks for taking the time to help me. First of all, I had to tweak the code a little bit. You were references sheets starting at Chart1, but I noticed that I don't have a "Chart1" in my file. The first chart tab is "Chart 5". So I just changed the code to reference charts:

Please Login or Register  to view this content.

I walked through the code until I came to the first error caused by the highlighted line:

Screenshot.GIF

I then ran the following code to unlock all charts and chart objects. Then I ran your code again, but unfortunately I still received the same error as abovec:

Please Login or Register  to view this content.

I'm lost as to where to go from here. Any ideas?

I'm afraid I have never encountered that sort of thing. I could apply protection to my chart sheet and get an error when trying to change the chart area, but the error would go away as soon as I unprotected the chart. A few tests I would suggest:

1) Did you try my code in a new, blank workbook that you had never applied any protection to?
2) Can you upload that new, blank test workbook so we can see if it behaves the same in our installations? Basically trying to flesh out if the problem is specific to a workbook, or if it is something built into the differences between 2007 and 2013, or something else.
3) I will see if I can get other users to test this to see if the behavior I see or the behavior you see is what they see.

From 2010 onwards, you may not resize the chart area for a chart sheet- https://support.microsoft.com/en-gb/...n-a-chartsheet

My apologies for leading you down the wrong path.
So it looks like we are back to using the margins. It seems to me that the needed margin sizes would depend entirely on the paper size you are using. Assuming A4 paper landscape orientation (29.7 x 21.0 cm), the margins would need to be:

left+right=29.7-22.0=7.7 cm divide this 7.7 up between left and rigth depending on exactly where you want the chart to appear on the page.
top+bottom=21.0-15.0=6.0 cm divide this 6.0 up between top and bottom depending on exactly where you want the chart to appear on the page.

(substitute the actual size of your paper for the assumed paper sizes I used).

As your original code shows, you should be able to use application.centimeterstopoints to convert these measurements to points.

Is that helpful?