Can't set manual HPageBreaks
 

Myrna -

I'm not sure it's a bug so much as a "feature" of the way XL
paginates. If my explanation below is even mostly correct, then at
the very least, the documentation in HELP is woefully inadequate.

AFAICT, XL has two primary ways of determining what will print -
reading the sheet's PageSetup.PrintArea property, or , if the
..PrintArea property is not set, by calculating the print area on the
fly from the Used Range or Selection.

While you can add HPageBreaks that fall outside the print area to
the HPageBreaks *collection*, the HPageBreaks *property* appears to
*return* only hpagebreaks from the collection that fall within the
determined print area. This makes some sense since XL by default
only prints the UsedRange, so returning an unused HPageBreak (i.e,
that lies outside the UsedRange) could lead to errors as well.

In your AddPageBreak routine, you successfully add a HPageBreak to
the HPageBreaks collection. However your ShowLocation routine's
results depend on what the print area is determined to be.

If you modify your AddPageBreak routine to set the print area to,
say, A1:J10, the hpagebreak you added to the collection is within
the .PrintArea, so you get the MsgBox in ShowLocation.

If you modify your AddPageBreak routine to put a value in, say A4,
the hpagebreak is within the calculated print area, so you get the
MsgBox in ShowLocation.

Of course, the "bug" part of this "feature" is that you can add to
the collection, but not necessarily determine what's been added, nor
delete it, unless you expand the UsedRange or .PrintArea. Again,
because XL paginates on the fly, this was probably a design choice -
better to return only relevant objects than spurious ones. It would
seem to me to be better to add another collection - FPageBreaks that
returns the pagebreaks fixed by the user, whether in the print area
or not...

In article ,
Myrna Larson wrote:

All of that said, I think you are a victim of the bugs in the internal code
associated with
HPageBreaks and VPageBreaks.

Can't set manual HPageBreaks
 

Thanks for your comments. I think you are correct on this -- I probably didn't have data below
the position of the break before setting it. Once I do that, ShowLocation shows the correct
location.

But there ARE problems with the PageBreaks collections. You will get a "bad subscript" error
when trying to access an existing break under the conditions described below (from KB article
218104):

"The active cell is above the horizontal page break or to the left of the vertical page break
that is referred to by the HPageBreaks or VPageBreaks index and the vertical or horizontal page
break location is off the screen to the right of the visible window or below the visible window
of the workbook."

This bug *could* cause problems when moving an existing page break, as the OP was trying to do,
depending on the location of the active cell when the code executes.

On Mon, 14 Jul 2003 12:39:37 -0600, "J.E. McGimpsey" wrote:

Myrna -

I'm not sure it's a bug so much as a "feature" of the way XL
paginates. If my explanation below is even mostly correct, then at
the very least, the documentation in HELP is woefully inadequate.

AFAICT, XL has two primary ways of determining what will print -
reading the sheet's PageSetup.PrintArea property, or , if the
.PrintArea property is not set, by calculating the print area on the
fly from the Used Range or Selection.

While you can add HPageBreaks that fall outside the print area to
the HPageBreaks *collection*, the HPageBreaks *property* appears to
*return* only hpagebreaks from the collection that fall within the
determined print area. This makes some sense since XL by default
only prints the UsedRange, so returning an unused HPageBreak (i.e,
that lies outside the UsedRange) could lead to errors as well.

In your AddPageBreak routine, you successfully add a HPageBreak to
the HPageBreaks collection. However your ShowLocation routine's
results depend on what the print area is determined to be.

If you modify your AddPageBreak routine to set the print area to,
say, A1:J10, the hpagebreak you added to the collection is within
the .PrintArea, so you get the MsgBox in ShowLocation.

If you modify your AddPageBreak routine to put a value in, say A4,
the hpagebreak is within the calculated print area, so you get the
MsgBox in ShowLocation.

Of course, the "bug" part of this "feature" is that you can add to
the collection, but not necessarily determine what's been added, nor
delete it, unless you expand the UsedRange or .PrintArea. Again,
because XL paginates on the fly, this was probably a design choice -
better to return only relevant objects than spurious ones. It would
seem to me to be better to add another collection - FPageBreaks that
returns the pagebreaks fixed by the user, whether in the print area
or not...

In article ,
Myrna Larson wrote:

All of that said, I think you are a victim of the bugs in the internal code
associated with
HPageBreaks and VPageBreaks.