Move/Resize PlotArea

[John]

I have code that creates as separate sheets (not on a worksheet). The charts
are included in paper reports and the users need to be able to hold different
chart up to the light to see how various cases compare or various results
from a similar case. The plots have the same X-Axis configuration (except
where it crosses the Y-Axis). However, the Y-Axis parameters are different
(scales, number format, etc.).
There may be up to 50 charts per case.

Therefore, the INSIDE plot area of the charts has to be at exactly the same
left, top coordinate and have exactly the same width and height. The
following codes gets it close. Why doesn't it make it exact?

Private Sub ResizePlot(oChart As Chart)
Dim PALt As Single, PATp As Single, PAWd As Single, PAHt As Single
Dim PAILt As Single, PAITp As Single, PAIWd As Single, PAIHt As Single
Dim PADLt As Single, PADTp As Single, PADWd As Single, PADHt As Single
With oChart
With .PlotArea
PALt = .Left
PATp = .Top
PAWd = .Width
PAHt = .Height
PAILt = .InsideLeft
PAITp = .InsideTop
PAIWd = .InsideWidth
PAIHt = .InsideHeight
PADLt = Application.InchesToPoints(1.25)
PADTp = Application.InchesToPoints(0.9)
PADWd = Application.InchesToPoints(7#)
PADHt = Application.InchesToPoints(6#)
.Left = PADLt - (PAWd - PAIWd)
.Top = PADTp - (PAITp - PATp)
.Width = PADWd + (PAWd - PAIWd)
.Height = PADHt + (PAHt - PAIHt)
End With
End With
End Sub

Also, I have noticed that the inside dimensions change when I add shapes to
the chart sheet. The Charts have to be landscape. However, there has to be
a header and footer on the right and left sides, rotated so that the page
appears in portrait. So I add AddTextEffect and rotate 90. When I do this
the plotarea dimensions change without a change to the chartarea.

I have all the AutoScaleFont values that I could find and use (ChartArea,
ChartTitle, AxisTitle (both), Legend, TickLabels) set to false and I do
nothing after running the above routine. However, plots with the
AddTextEffect have different dimensions that plots without AddTextEffect.

Windows XP, Excel 2002 SP-2

Thanks,
John

 

[John]

Further information:

Adding the AddTextEffect had no affect. In the same routine I added the
AddTextEffect I also changed the ChartTitle Text. The original text has two
lines with the second line having a smaller font. When the ChartTitle text
is changed the font of the entire title reverts to the larger font defined
for the object. I then reset the second line font. As a result the PlotArea
top moves and resizes in height.

Running the previously supplied code moves the plot area to a different
position than if run before the ChartTitle change and different from running
it without the ChartTitle change

John

 

[Tushar Mehta]

XL has its own opinion on how your chart should be displayed. In the
few instances where I want to do things like you want, I use that code
at the very end and loop it a couple (or three) times. While most
would believe it makes absolutely no sense to run the same code
multiple times, when dealing with XL charts it works. Sometimes.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[John]

I have looped it as many as 4 times as the last activity related to the
chart. The results reach an equalibrium. However, they will be different
from chart to chart. Additionally, I may have to rechart as many as 50
charts at a shot, which takes enough time as it is.

Why should the plotarea change just because you change the charttitle? It
looks like it tries to maintain at least the margin it had before the change.
However, that is not the only contributing factor.

John