Thread: Move/Resize PlotArea

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

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

"John" wrote:

> 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

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

In article <39EFA515-F0ED-4AE5-B670-5C1967A4C7B6@microsoft.com>,
John@discussions.microsoft.com says...
> 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
>

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


"Tushar Mehta" wrote:

> 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
>
> In article <39EFA515-F0ED-4AE5-B670-5C1967A4C7B6@microsoft.com>,
> John@discussions.microsoft.com says...
> > 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
> >
>