Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
arranging charts and visible screen size
i have a set of procedures that output multiple charts to a particular
worksheet. At the moment all the charts are centered on the sheet on top of each other. I'm thinking this is a long shot, but is there a way to automatically (and programmatically) arrange these charts on the page? Kind of like what Windows/Arrange.../Tiled does for workbooks? I think more realistic would be doing the math myself to arrange the charts. So to that end, how would I find the size of the visible area of the spreadsheet (the part not including the toolbars, menu bar, etc etc). I would use this to set the Top, Left, Height, Width properties of the charts. Anyone have a better way of doing this or already have some code that does something similiar? Thanks a lot guys. -- ThatFella |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
arranging charts and visible screen size
You're using
Charts.Add and later ActiveChart.Location Whe=xlLocationAsObject, Name:="Sheet1" to make your charts, right? Instead of this, you can add the chart objects directly to the worksheet: With ActiveSheet.ChartObjects.Add _ (Left:=100, Width:=375, Top:=75, Height:=225) .Chart.ChartType = xlXYScatterLines .Chart.SetSourceData Source:=Sheets("Sheet1").Range("A3:G14") End With You can determine the precise coordinates of your chart in the worksheet, measured in points from the top left of cell A1. ActiveWindow.VisibleRange tells you which cells are visible, including the portions of the right column and bottom row which are not completely visible, so use this to get an idea how large the charts must be to be made to fit. This code makes a number of charts, lined up in a nice array, but it extends beyond the visible window. Perhaps you can combine the discussion above with the code below: Sub ManyCharts() Dim plotRange As Range, chtObj As ChartObject Dim nPts As Integer Dim ichart As Integer Dim chartHt As Integer, chartWd As Integer Set plotRange = ActiveSheet.Range("A1:A5") nPts = 5 chartHt = 150 chartWd = 250 For ichart = 1 To 24 ' 3 wide by 8 high Set chtObj = ActiveSheet.ChartObjects.Add( _ Left:=ActiveSheet.Range("B8").Left + _ Int((ichart - 1) / 8) * chartWd, _ Top:=ActiveSheet.Range("B8").Top + _ ((ichart - 1) Mod 8) * chartHt, _ Width:=chartWd, _ Height:=chartHt) Debug.Print chtObj.Name & vbCrLf & chtObj.Parent.Name chtObj.Chart.ChartWizard _ Source:=plotRange.Offset((ichart - 1) * nPts, 0), _ Title:="Chart " & ichart With chtObj.Chart ' Do your formatting in here .HasLegend = False .Axes(1, 1).HasTitle = True .Axes(1, 1).AxisTitle.Text = "Categories" ' etc. End With Next End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ ThatFella wrote: i have a set of procedures that output multiple charts to a particular worksheet. At the moment all the charts are centered on the sheet on top of each other. I'm thinking this is a long shot, but is there a way to automatically (and programmatically) arrange these charts on the page? Kind of like what Windows/Arrange.../Tiled does for workbooks? I think more realistic would be doing the math myself to arrange the charts. So to that end, how would I find the size of the visible area of the spreadsheet (the part not including the toolbars, menu bar, etc etc). I would use this to set the Top, Left, Height, Width properties of the charts. Anyone have a better way of doing this or already have some code that does something similiar? Thanks a lot guys. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problems with Visible and Screen | Excel Discussion (Misc queries) | |||
change font size on screen, but prints in old size | Excel Discussion (Misc queries) | |||
Work sheet size relative to screen size | Excel Discussion (Misc queries) | |||
arranging excel tabs (charts vs worksheets)in multiple rows/dimens | Charts and Charting in Excel | |||
color not visible on screen | Setting up and Configuration of Excel |