Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Page Break: Format Row
In the following macro I have inserted a print preview to reset the
horizontal page break counter in the spreadsheet. Without this accomodation I can only run the macro once. The second time I run it, I get an out of range error. Is there another way to reset the horizontal page break so that each time the macro runs it starts recognizing the horizontal page breaks from the beginnging of the spreadsheet? Sub LineFormatAtPageBreak() Dim oHPgbr As HPageBreak ActiveWindow.SelectedSheets.PrintPreview For Each oHPgbr In ActiveSheet.HPageBreaks MsgBox "Other row page break at:" & oHPgbr.Location.Address ' Format row at page break Next End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Page Break: Format Row
Hi JR (OK to call you that?),
I haven't yet worked out exactly why it behaves this way - I found the same thing - but I believe this shoul work, it solved it for me. Sub LineFormatAtPageBreak() Dim oHPgbr As HPageBreak Debug.Print ActiveSheet.HPageBreaks.Count ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).Select ActiveSheet.Cells(1, 1).Select For Each oHPgbr In ActiveSheet.HPageBreaks MsgBox "Other row page break at: " & oHPgbr.Location.Address ' Format row at page break Next End Sub I have used the Debug line to force the pagebreaks to show before the For Each line. Anything that forces these to appear will do. Then I have selected the last cell in column 1, before returning to the top. It appears that once a cell has been selected below the final HPageBreak the problem goes. So far I have tested a few times with different scenarios and this hasn't generated an error yet. If I find out why then I'll let you know. If anyone else can explain in detail then I'd be interested to find out! Sean. "JR_06062005" wrote: In the following macro I have inserted a print preview to reset the horizontal page break counter in the spreadsheet. Without this accomodation I can only run the macro once. The second time I run it, I get an out of range error. Is there another way to reset the horizontal page break so that each time the macro runs it starts recognizing the horizontal page breaks from the beginnging of the spreadsheet? Sub LineFormatAtPageBreak() Dim oHPgbr As HPageBreak ActiveWindow.SelectedSheets.PrintPreview For Each oHPgbr In ActiveSheet.HPageBreaks MsgBox "Other row page break at:" & oHPgbr.Location.Address ' Format row at page break Next End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Page Break: Format Row
Thanks, that worked
"SeanC UK" wrote: Hi JR (OK to call you that?), I haven't yet worked out exactly why it behaves this way - I found the same thing - but I believe this shoul work, it solved it for me. Sub LineFormatAtPageBreak() Dim oHPgbr As HPageBreak Debug.Print ActiveSheet.HPageBreaks.Count ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).Select ActiveSheet.Cells(1, 1).Select For Each oHPgbr In ActiveSheet.HPageBreaks MsgBox "Other row page break at: " & oHPgbr.Location.Address ' Format row at page break Next End Sub I have used the Debug line to force the pagebreaks to show before the For Each line. Anything that forces these to appear will do. Then I have selected the last cell in column 1, before returning to the top. It appears that once a cell has been selected below the final HPageBreak the problem goes. So far I have tested a few times with different scenarios and this hasn't generated an error yet. If I find out why then I'll let you know. If anyone else can explain in detail then I'd be interested to find out! Sean. "JR_06062005" wrote: In the following macro I have inserted a print preview to reset the horizontal page break counter in the spreadsheet. Without this accomodation I can only run the macro once. The second time I run it, I get an out of range error. Is there another way to reset the horizontal page break so that each time the macro runs it starts recognizing the horizontal page breaks from the beginnging of the spreadsheet? Sub LineFormatAtPageBreak() Dim oHPgbr As HPageBreak ActiveWindow.SelectedSheets.PrintPreview For Each oHPgbr In ActiveSheet.HPageBreaks MsgBox "Other row page break at:" & oHPgbr.Location.Address ' Format row at page break Next End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 Page Break Adjustments causes a page break each cell | Excel Worksheet Functions | |||
How do I do page breaks when view menu doesnt page break preview | Excel Discussion (Misc queries) | |||
change and/or remove page number watermark in page break preview | Excel Discussion (Misc queries) | |||
Hiding the page number in page break preview background. | Excel Discussion (Misc queries) | |||
adding a new page break to an existing page break | Excel Discussion (Misc queries) |