A problem with HPageBreaks

N

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.
 
T

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
 
L

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top