| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
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 |
|
#2
|
|||
|
|||
|
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 |
|
#4
|
|||
|
|||
|
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 , 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Hybrid Mode
