Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Manual pie charts | Charts and Charting in Excel | |||
Intermittent Error - Method 'Add' of object 'HPageBreaks' failed | Excel Worksheet Functions | |||
how can i get MANUAL CALCULATIONS p e r m a n e n t l y | Setting up and Configuration of Excel | |||
Is there no one who uses PageBreaks? HPageBreaks? | Excel Worksheet Functions | |||
HPageBreaks.Add | Excel Worksheet Functions |