Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting PageBreaks | Excel Programming | |||
Deleting PageBreaks | Excel Programming | |||
Is there no one who uses PageBreaks? HPageBreaks? | Excel Worksheet Functions | |||
pagebreaks | Excel Programming | |||
Excel pagebreaks | Excel Programming |