A problem with HPageBreaks

[NateBuckley]

Hello is there an issue with playing with HPageBreaks, I have the following
code

Sub newPageIfRangeOver(rng as Range)

Dim pageBreaks As HPageBreaks
Dim pageBreak As HPageBreak
Set pageBreaks = Sheets(rng.Worksheet.name).HPageBreaks

For Each pageBreak In pageBreaks
...
Next pageBreak
End Sub

This Sub is called a number of times depending on data recieved, it has
started to just say "Subscript out of range" yet if I step through the the
program slowley, it works, no error. I'm just wondering if there is an error
with the HPageBreak Object that I perhaps I have overlooked.

Thanks in advance for any help thrown my way.

 

[TomPl]

Playing with page breaks is a very tricky affair because the automatic page
breaks are generated by excel and are not static. The "Subscript out of
range" might be because excel has not updated the page breaks. I find that
when I open a workbook excel does not see all of the page breaks until I
actually schroll down to the end of the document. Once I schroll down the
automatic page breaks are available to VGA.

A trick that I have used is to switch to "Page Break Preview Mode" which
seems to resolve the problem. Switching to a from "Page Break Preview Mode"
can be done within your code to avoid the hassle of doing it manually.

Hope this helps.

Tom

 

[NateBuckley]

Seems to have done the trick, thanks alot matey.

I owe you a cold frosty beer.

 

[Larry]

Hello,

I got the trick too. I try to access the Excel Worksheet’s HPageBreaks. I
can get the COUNT of the HPageBreaks. But when I start to loop the pagebreaks
by using a integer index and the index > 1, Microsoft.Office.Interop.Excel
always give me error: “invalid index†(The object has 17 pagebreaks). It can
get the first pagebreak in the pagebreaks only, not the others. Do you think
this is also related to "Page Break Preview Mode" ?

Thanks,
Larry