insert pagebreak if chart is not fully displayed

[tpoynton]

Greetings - venturing into new territory again...and I've been surprised that searches here and on Google have not yeilded anything. Perhaps I have my search terms all wrong...

I'm generating a report that has text and charts. The charts are of a fixed size, and I've got the .fitpageswide property to deal with wide columns if they arise, but I'm only using the first 5 columns, and only one of them is variable.

anyway, the number of rows can be highly variable. What I'd like to do is insert a pagebreak before a chart if the chart is not fully displayed on a page.

I can set things up so that 3 charts fit on a page (there are 7)...so one option might be to see how many rows there are before the charts, and simply start all charts from a new page at a known starting point.

[tpoynton]

OK, found a solution that works...I ended up embedding it in the sub I call to create the graphs in the report. I'll put the code below, with comments to clarify...not so pretty, but it works.
[vba]
Public Sub createGraph(chartRange As Range, reportSht As Worksheet)
'creates chart at end of active range
Dim chartTop As Double
Dim cell As Range
Dim i As Long
Dim breakRange As String
Dim breakLoc As Range
For Each cell In reportSht.Range(Cells(1, 1) _
, Cells(reportSht.UsedRange.Rows.Count + 1, 1))
chartTop = chartTop + cell.RowHeight
Next cell
With reportSht
'used points instead of rows as some rowheights differ on page 1
'this adds first page break
If chartTop > 425 Then
If .HPageBreaks.Count <= 1 Then
breakRange = "B" & .UsedRange.Rows.Count + 2
.HPageBreaks.Add .Range(breakRange)
Else
Set breakLoc = .HPageBreaks.Item(.HPageBreaks.Count).Location
'each chart consumes about 20 rows; this sets page break
'after second chart on a page
If .UsedRange.Rows.Count - breakLoc.Row > 22 Then
.HPageBreaks.Add .Range(.Cells(.UsedRange.Rows.Count + 1, 2) _
, .Cells(.UsedRange.Rows.Count + 1, 2))
End If
End If
End If
End With
'creates the chart
With reportSht.ChartObjects.Add _
(reportSht.Columns(1).Width, chartTop, 350, 250)
.Chart.ChartWizard chartRange, xlColumn, , xlColumns, 1, 1, False, _
chartRange.Cells(1, 1).Offset(-1, 0), chartRange.Cells(1, 1)
With .Chart.PlotArea
.Border.LineStyle = xlNone
.Interior.ColorIndex = xlNone
End With
.RoundedCorners = True
With .Chart.Axes(xlValue)
.HasMajorGridlines = False
.TickLabels.NumberFormat = "General"
.HasTitle = True
End With
.Chart.Axes(xlValue).AxisTitle.Characters.Text _
= .Chart.SeriesCollection(1).Name
End With
'put marker under chart's bottom right cell
With reportSht.ChartObjects(reportSht.ChartObjects.Count).BottomRightCell
.Value = "Chart Marker"
.Font.Color = vbWhite
End With
End Sub
[/vba]

still open to suggestions! Now off to learn about creating this in Word...

[rory]

Put the charts on their own sheet?

[tpoynton]

Thanks Rory...if the charts are on their own sheets, how can I include them in a printable report? right now, I've got it setup so that one new sheet is created, with the print area set so people just need to click print.

[rory]

Well if the charts are on their own sheet, you can size and print them as you like. If you need them to be in the middle of your data, then obviously this won't be much use.
You can add your own print menu that prints the data including the chart sheet.