Prevent Page Break across a Cell

[Eric Weller]

All:
I have a program that is inserting several hundred jpg pictures into an
Excel Template. Each picture goes into a merged cell that is 4 columns wide
and 17 rows high.
The problem is that when Excel is automatically placing page breaks, it
breaks across the merged cell so that part of the picture is on one page and
the rest is on the next page.
I know in Word you can specify that a table where it will not break across a
cell but i do not know how to do that in Excel.
I appreciate any suggestions!

 

[dan dungan]

How does your program automatically create the page breaks?

 

[dan dungan]

I found this in this newsgroup from 2005:

Hi All,

I am trying to set up the horizontal page breaks in a worksheet
automatically, so that they never break across a merged cell.

The merged cells are in column C.

The code I have written is below.

It correctly identifies where the first page break occurs, but it
won't actually change the location of that break.

Upon testing, in my immediate window, with the first (and only)
horizontal break at A85, I get a repeating list as follows:

BreakCell = $A$85
NewBreak = $A$86
MyBreak = $A$85

BreakCell = $A$85
NewBreak = $A$86
MyBreak = $A$85

..... and so on forever looping

The issue therefore appears to be that the code is failing to actually
change the horizontal break location. The location property help
explicitly states that it is read / write, but I am not sure what to
do from here.

Any suggestions are appreicated.

Thanks,

Alan.

+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+

Option Explicit

Sub FixHBreaks()

Dim MyHBreak As HPageBreak
Dim BreakCell As Range
Dim ReRun As Boolean
Dim NewBreak As Range

ActiveSheet.Unprotect

Do

ReRun = False

For Each MyHBreak In ActiveSheet.HPageBreaks

Set BreakCell = MyHBreak.Location

Debug.Print "BreakCell = " & BreakCell.Address

If BreakCell.Offset(0, 2).MergeArea.Rows.Count > 1 Then

Set NewBreak = MyHBreak.Location.Offset(1, 0)

' This next line appears to be inneffective

MyHBreak.Location = NewBreak

' These next three lines generate the info in the immediate window

Debug.Print "NewBreak = " & NewBreak.Address
Debug.Print "MyBreak = " & MyHBreak.Location.Address
Debug.Print

ReRun = True

End If

Next

Loop Until ReRun = False

End Sub

+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+



Tom Ogilvy
Sep 25 2005, 7:06 pm
Newsgroups: microsoft.public.excel.programming
From: "Tom Ogilvy" <[email protected]>
Date: Sun, 25 Sep 2005 22:06:06 -0400
Local: Sun, Sep 25 2005 7:06 pm
Subject: Re: Avoiding page breaks across merged cells - Code not
working as expected

You can't set the pagebreak beyond the default/automatic pagebreak.
The
reason you have a default/automatic pagebreak is because the hardware
limitations on the printer demand a pagebreak at that location. You
can
insert a pagebreak before it, but anything set after it will not
affect it.

 

[Eric Weller]

I am not inserting any page breaks programatically. I am relying on Excel to
handle page breaking. However in the template, I do specify that certain
rows are repeated on each page.
I want to keep my program from having to insert page breaks and have Excel
handle that.
Thanks!

 

[Eric Weller]

I am reading that this cannot be corrected without programatically inserting
a page break. Is that correct?

 

[dan dungan]

I don't know if that's true, but it seems that way from the small
amount of research I've done.