HOW DO I DISABLE AUTOMATIC PAGE BREAKS IN EXCEL

[2314]

HOW DO I DISABLE AUTOMATIC PAGE BREAKS IN EXCEL OR HOW CAN I KNOW AN AUTOMATIC PAGE BREAK HAS OCCURED IN EXCEL FROM VBA

 

[PHV]

Please don't shout when posting, ie avoid writing all in caps.
Take a look at the Worksheet.VPageBreaks collection and the VPageBreak.Type property (xlPageBreakAutomatic).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[2314]

thanks what i would like to know whether there is an event which fires on a page break

 

[macropod]

Unless you set a page break manually to the same or a smaller size that the ones Excel chooses, Excel creates its own page breaks based on the paper and margin dimensions defined for the page and the number of rows & columns that will fit within those dimensions.

Cheers

 

[xlbo]

and no, there is no event that fires on a page break - excel doesn't treat page breaks as an "event" - more as formatting really

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[2314]

hi i am getting the error when i add pagebreak and run excel from vb the first time it runs ok this error appears only next time even though i am properly closing excel

err number:=-2147417851
err description:=Method 'Add' of object 'HPageBreaks' failed

can u guys help out

 

[xlbo]

try posting the code that errors - we ain't mind readers y'know !

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[2314]

here is the code

ex.Rows(i & ":" & i).Select
ex.ActiveWindow.SelectedSheets.HPageBreaks.Add before:=ActiveCell

wherein i value is that of an integer
and ex is an instance of excel application

 

[xlbo]

You can't use SelectedSheets for this as it doesn't return a specific sheet and there can only be 1 active cell at any 1 time. Therefore, you must loop eg

for each sht in selectedsheets
  sht.hpagebreaks.add before:=activecell
next

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question