arranging charts and visible screen size

  • Thread starter Thread starter ThatFella
  • Start date Start date
T

ThatFella

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.
 
You're using

Charts.Add

and later

ActiveChart.Location Where:=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
 
Back
Top