Putting pagebreaks after objects
 

Hi again

I've got a page that has lots of charts on it, stacked above each other
vertically. When I want to print the page, Excel has a habit of cutting
the occasional unlucky graph in half :D

So, to help stop this needless charticide, I want to add some page
breaks. Specifically, one after every 8 charts. This I thought would be
easy when I saw the HPageBreaks property. Great, I thought, after every
8th chart I'll just add one of these with

Code:
--------------------

ActiveSheet.HPageBreaks.Add After:=ActiveChart

--------------------


Naturally, it wasn't so cooperative, and gave me big, unrecoverable
errors. Any idea why? Presumably it's because it wants a cell, rather
than an object, as a location reference. Which suggests another way -
is there any way to determine the row onwhich the chart's lower edge
can be found? Then I could add a pagebreak to a cell on this row, which
will probably be more agreeable to VB.

Thanks.


--
F*SH
------------------------------------------------------------------------
F*SH's Profile: http://www.excelforum.com/member.php...o&userid=36355
View this thread: http://www.excelforum.com/showthread...hreadid=561958

Putting pagebreaks after objects
 

This may give you an idea of what to do:

Sub shpe()
Dim shp As Integer
Dim c As Long
shp = Sheet1.Shapes.Count
c = Sheet1.Shapes(2).BottomRightCell.Row
Sheet1.HPageBreaks.Add befo=Rows(c + 1)
End Sub

I ran out of time to work on this for you, will check back later,
Mike F
"F*SH" wrote in message
...

Hi again

I've got a page that has lots of charts on it, stacked above each other
vertically. When I want to print the page, Excel has a habit of cutting
the occasional unlucky graph in half :D

So, to help stop this needless charticide, I want to add some page
breaks. Specifically, one after every 8 charts. This I thought would be
easy when I saw the HPageBreaks property. Great, I thought, after every
8th chart I'll just add one of these with

Code:
--------------------

ActiveSheet.HPageBreaks.Add After:=ActiveChart

--------------------


Naturally, it wasn't so cooperative, and gave me big, unrecoverable
errors. Any idea why? Presumably it's because it wants a cell, rather
than an object, as a location reference. Which suggests another way -
is there any way to determine the row onwhich the chart's lower edge
can be found? Then I could add a pagebreak to a cell on this row, which
will probably be more agreeable to VB.

Thanks.


--
F*SH
------------------------------------------------------------------------
F*SH's Profile:
http://www.excelforum.com/member.php...o&userid=36355
View this thread: http://www.excelforum.com/showthread...hreadid=561958

Putting pagebreaks after objects
 

Try this out:

Sub ShapeBreak8()
Dim shpcnt As Long
Dim shprow As Long, i As Long

ActiveSheet.PageSetup.PrintArea = ""
shpcnt = Sheet1.Shapes.Count

For i = 1 To shpcnt
If i Mod 8 = 0 Then
shprow = Sheet1.Shapes(i).BottomRightCell.Row
Sheet1.HPageBreaks.Add befo=Rows(shprow + 1)
End If
Next i
End Sub

Mike F
"Mike Fogleman" wrote in message
m...
This may give you an idea of what to do:

Sub shpe()
Dim shp As Integer
Dim c As Long
shp = Sheet1.Shapes.Count
c = Sheet1.Shapes(2).BottomRightCell.Row
Sheet1.HPageBreaks.Add befo=Rows(c + 1)
End Sub

I ran out of time to work on this for you, will check back later,
Mike F
"F*SH" wrote in message
...

Hi again

I've got a page that has lots of charts on it, stacked above each other
vertically. When I want to print the page, Excel has a habit of cutting
the occasional unlucky graph in half :D

So, to help stop this needless charticide, I want to add some page
breaks. Specifically, one after every 8 charts. This I thought would be
easy when I saw the HPageBreaks property. Great, I thought, after every
8th chart I'll just add one of these with

Code:
--------------------

ActiveSheet.HPageBreaks.Add After:=ActiveChart

--------------------


Naturally, it wasn't so cooperative, and gave me big, unrecoverable
errors. Any idea why? Presumably it's because it wants a cell, rather
than an object, as a location reference. Which suggests another way -
is there any way to determine the row onwhich the chart's lower edge
can be found? Then I could add a pagebreak to a cell on this row, which
will probably be more agreeable to VB.

Thanks.


--
F*SH
------------------------------------------------------------------------
F*SH's Profile:
http://www.excelforum.com/member.php...o&userid=36355
View this thread:
http://www.excelforum.com/showthread...hreadid=561958

Putting pagebreaks after objects
 

Better add code to delete all HPageBreaks first
with ActiveSheet
'Speed up display
.displaypagebreaks=false
.ResetAllPageBreaks

Also
For i = 1 To shpcnt
If i Mod 8 = 0 Then
can be written
For i = 1 To shpcnt step 8

'Reset to see page breaks
ActiveSheet.displaypagebreaks=true

NickHK

"Mike Fogleman" wrote in message
m...
Try this out:

Sub ShapeBreak8()
Dim shpcnt As Long
Dim shprow As Long, i As Long

ActiveSheet.PageSetup.PrintArea = ""
shpcnt = Sheet1.Shapes.Count

For i = 1 To shpcnt
If i Mod 8 = 0 Then
shprow = Sheet1.Shapes(i).BottomRightCell.Row
Sheet1.HPageBreaks.Add befo=Rows(shprow + 1)
End If
Next i
End Sub

Mike F
"Mike Fogleman" wrote in message
m...
This may give you an idea of what to do:

Sub shpe()
Dim shp As Integer
Dim c As Long
shp = Sheet1.Shapes.Count
c = Sheet1.Shapes(2).BottomRightCell.Row
Sheet1.HPageBreaks.Add befo=Rows(c + 1)
End Sub

I ran out of time to work on this for you, will check back later,
Mike F
"F*SH" wrote in
message
...

Hi again

I've got a page that has lots of charts on it, stacked above each other
vertically. When I want to print the page, Excel has a habit of cutting
the occasional unlucky graph in half :D

So, to help stop this needless charticide, I want to add some page
breaks. Specifically, one after every 8 charts. This I thought would be
easy when I saw the HPageBreaks property. Great, I thought, after every
8th chart I'll just add one of these with

Code:
--------------------

ActiveSheet.HPageBreaks.Add After:=ActiveChart

--------------------


Naturally, it wasn't so cooperative, and gave me big, unrecoverable
errors. Any idea why? Presumably it's because it wants a cell, rather
than an object, as a location reference. Which suggests another way -
is there any way to determine the row onwhich the chart's lower edge
can be found? Then I could add a pagebreak to a cell on this row, which
will probably be more agreeable to VB.

Thanks.


--
F*SH

------------------------------------------------------------------------
F*SH's Profile:
http://www.excelforum.com/member.php...o&userid=36355
View this thread:
http://www.excelforum.com/showthread...hreadid=561958