Change chart height without resizing plotArea

Question

I am trying to resize an excel chart with VBA.

Is it possible to change ActiveChart.ChartArea.Height without affecting the size of the PlotArea? Whenever I attempt to change the chart height it looks like the plot area is automatically resized, which is an undesired outcome.

I have tried the following sequence, when downsizing a graph:

Changing plotarea to fixed desired height;
Changing chart height to fixed desired height;
Changing plotarea to fixed desired height;

This sequence does not yield expected results, as (1) the chart is not changed to specified height, and (2) the plotarea height is output correctly, but its positioning within the chart (.InsideTop) has changed.

FaneDuru · Accepted Answer · 2022-02-15 19:05:19Z

2

Please, test the next way of dealing with a chart dimensions. The scenario involves the next process: firstly memorizing the PlotArea dimensions (Height/Width), then play with the chart (Object) dimensions, reset the PlotArea ones and set its Position to Automatic. Excel tries guessing what you want accomplishing and it looks/is more probable that both chart elements to be modified proportionally:

Sub testActiveChartDimensions()
  Dim ch As Chart, plHeight As Double, plWidth As Double
  Set ch = ActiveChart 'plays with a selectded chart

  plHeight = ch.PlotArea.height: plWidth = ch.PlotArea.width 'memorize the plot area dimensions

  ch.Parent.height = ch.Parent.height * 2: ch.Parent.width = ch.Parent.width * 2 'resize the chartObject
  
  ch.PlotArea.height = plHeight: ch.PlotArea.width = plWidth 'reset the initial dimensions for plot area
                                                             ' you can set any other dimensions (just to be lower than the new chart dimensions...)
  ch.PlotArea.Position = xlChartElementPositionAutomatic     'center it on the chart object
End Sub

edited Feb 15, 2022 at 19:05

answered Feb 15, 2022 at 13:56

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

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

10 Comments

FaneDuru Over a year ago

@Noobster No problem, it may happen... I will edit it, but if you do not find some time to come back, it will not be a problem, at all. I do not care too much about notoriety. I tried explaining in a comment about what is to be done and tried putting it in a piece of code, not being sure that I was clear enough. Anyhow, glad I could help!

chris neilsen Over a year ago

@Noobster I'm confused, how is this the answer. OP states ... the plot area is automatically resized, which is an undesired outcome. So I assume you want the Plot area to remain at its original size. For me (using Excel 365) changing .PlotArea.Position = xlChartElementPositionAutomatic also changes the plot area size to fill the chart

FaneDuru Over a year ago

@chris neilsen Nop... Using .PlotArea.Position = xlChartElementPositionAutomatic after setting the PlotArea old dimensions it only centers the PlotArea inside the chartObject.

chris neilsen Over a year ago

I tested on a line chart, and your exact code. It wouldn't be the first time MS introduced a bug between versions

FaneDuru Over a year ago

@chris neilsen It may be an explanation... :) I tested the code on a line chart and it behaves as it should. Or, how it behaves on the other types on my installations...

|

Collectives™ on Stack Overflow

Change chart height without resizing plotArea

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related