How to set chart PlotArea to marry up with fixed rectangle, for any screen resolution

[Charles Jordan]

Hi - we have been plagued by a chart plotarea problem for five long
years, in Excel 95, 97, 2000, and probably XP(10), and I would like to
offer the NG the guts of the *terrible* method we have had to devise
to get round it, and ask whether any one has a better solution. Our
terrible approach is in part (B) of ths posting.....BUT:-

In 2003-01-27, Andy Pope very kindly contributed a method adjusting a
shape to fit the .plotarea.inside dimensions, but we want to invert
that and marry the plotarea dimensions to a *fixed transparent
rectangle's borders* (a template), using a "Do while (or Until)
construct). So, given a rectangle whose (hardcoded) .top, .left,
..width and .height are 21, 556.5, 267,and 239.5 (for example), if we
first shrink the plotarea to the middle of the chartobject, how can we
expand it until it marries ? (Note that the .plotarea.inside
dimensions are still read only ? Microsoft, maybe you remembered it in
version 10 ?


Thanks in advance

Charles Jordan

'--------------------------------------------------------
(B) This method works, and consists of the following:-

(1) constructing a Lookup sheet, which is a matrix holding, for every
chart, the desired plotarea dimensions for each combination of
Excel/and screen resolution. (S0, 4 versions of Excel times eight
resolutions altogether, growing daily (800X600, 1024x768 etc)

(2) We first set and lock the dimensions of a transparent rectangle
with a thin yellow border, *up to which every plotarea has to marry,
regardless*.It's a template.

(3) The Lookup sheet is constructed, and the entry of its contents
automated by using a custom dialogsheet populated with scroll bars. We
call up this logsheet, target a chartobject (and its chart)and using
the scroll bars adjust the plotarea dimensions until they marry with
the borders of the fixed rectangle. Each time we adjust a scroll bar
on the dialogsheet (and thus the plotarea), it writes the plotarea
setting to the appropriate cell in the Lookup sheet.

(4)Then upon startup, Auto_Open establishes both the Excel version and
the screen res which it writes to Ranges "Addrows" and "VideoColumn",
and then

(5)it calls a Sub CalibratePlots which for every chart does the lookup

Here is the code where we fix Range "Addrows" - the settings for the
Excel version

ExcelVersion% = Val(Application.Version)'a variable set to capture the
version
Select Case ExcelVersion%
Case Is = 7
Range("Addrows").Value = 0 'used to set Chart.Plotareas
for Excel version 7
Case Is = 8
Range("Addrows").Value = 24
Case Is = 9
Range("Addrows").Value = 48
Case Is > 9
Range("Addrows").Value = 48 'For Excel version 10
End Select '(this section tells the Sub CalibratePlots which row
to look dowm to in the Lookup sheet (which is called "PlotSettings")


Here is the code where we fix Range "VideoColumn" - the settings for
the screen resolution

Select Case Application.UsableWidth
Case Is < 650
Range("VideoColumn").Value = 5 'for 800x600, picks up
Usablewidth = 597
Case Is < 800
Range("VideoColumn").Value = 6 'for 1024x768, picks up
Usablewidth = 765
Case Is < 900
Range("VideoColumn").Value = 7 'for 1152x864, picks up
Usablewidth = 861
Case Is < 1000
Range("VideoColumn").Value = 8 'for 1280x1024, picks up
Usablewidth = 957
Case Is < 1100
Range("VideoColumn").Value = 9 'for 1400x1200, picks up
Usablewidth = 1044
Case Is < 1259
Range("VideoColumn").Value = 10 'for 1600x1200, picks up
Usablewidth = 1197
End Select

'Here is the code where we lookup the lookup table upon startup
Sub CalibrateSheet1Plots()
Dim Ad As Integer, Vid As Integer
Set PlotSheet = Worksheets("PlotSettings") 'this holds the
settings
Set Mysheet = Worksheets("Sheet1")
Set Mychart = Mysheet.ChartObjects("Chart 1")
Application.ScreenUpdating = False

Ad = Range("Addrows").Value
Vid = Range("VideoColumn").Value
Mychart.Activate'You MUST activate the chartobject to do
plotarea surgery
With Mychart.Chart.PlotArea 'and activate Mysheet afterwards
.Width = .Width - 100
.Height = .Height - 100
.Top = PlotSheet.Cells(4 + Ad, Vid).Value
.Left = PlotSheet.Cells(5 + Ad, Vid).Value
.Width = PlotSheet.Cells(6 + Ad, Vid).Value
.Height = PlotSheet.Cells(7 + Ad, Vid).Value
End With
Mysheet.activate:Range("A1").select
End sub

 

[Stephen Bullen]

Hi Charles,

In 2003-01-27, Andy Pope very kindly contributed a method adjusting a
shape to fit the .plotarea.inside dimensions, but we want to invert
that and marry the plotarea dimensions to a *fixed transparent
rectangle's borders* (a template), using a "Do while (or Until)
construct). So, given a rectangle whose (hardcoded) .top, .left,
..width and .height are 21, 556.5, 267,and 239.5 (for example), if we
first shrink the plotarea to the middle of the chartobject, how can we
expand it until it marries ?

The following works for me for all versions apart from Excel 2000 (it seems to get the PlotArea.InsideHeight wrong!):

Sub Test()

Dim oCht As Chart, oPA As PlotArea
Dim oRec As Rectangle

Set oCht = ActiveSheet.ChartObjects(1).Chart
Set oPA = oCht.PlotArea
Set oRec = oCht.Rectangles(1)

oPA.Height = oRec.Height / 2
oPA.Width = oRec.Width / 2

For i = 1 To 4
If Abs(oRec.Top - oPA.InsideTop) > 0.5 Then
oPA.Top = oPA.Top + (oRec.Top - oPA.InsideTop)
End If

If Abs(oRec.Left - oPA.InsideLeft) > 0.5 Then
oPA.Left = oPA.Left + oRec.Left - oPA.InsideLeft
End If

If Abs(oRec.Width - oPA.InsideWidth) > 0.5 Then
oPA.Width = oPA.Width + (oRec.Width - oPA.InsideWidth)
End If

If Abs(oRec.Height - oPA.InsideHeight) > 0.5 Then
oPA.Height = oPA.Height + (oRec.Height - oPA.InsideHeight)
End If
Next
End Sub

The For loop is there to accomodate automatic font scaling - as the plot area is increased in size, the fonts get bigger, so taking
up more room and making the Insidexxx dimensions smaller.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk