Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set chart PlotArea to marry up with fixed rectangle, for any screen resolution
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set chart PlotArea to marry up with fixed rectangle, for any screen resolution
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable screen size, not resolution | Excel Worksheet Functions | |||
Excel 2003 rdp screen resolution | Excel Discussion (Misc queries) | |||
How to get fixed size for every screen resolution? | Charts and Charting in Excel | |||
How to change resolution of monitor/screen | Excel Programming | |||
Excel screen resolution query | Excel Programming |