Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent Page Break across a Cell
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent Page Break across a Cell
How does your program automatically create the page breaks?
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent Page Break across a Cell
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" Date: Sun, 25 Sep 2005 22:06:06 -0400 Local: Sun, Sep 25 2005 7:06 pm Subject: 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. -- Regards, Tom Ogilvy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent Page Break across a Cell
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent Page Break across a Cell
I am reading that this cannot be corrected without programatically inserting
a page break. Is that correct? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent Page Break across a Cell
I don't know if that's true, but it seems that way from the small
amount of research I've done. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Every cell has a page break?!?! | Excel Discussion (Misc queries) | |||
Excel 2007 Page Break Adjustments causes a page break each cell | Excel Worksheet Functions | |||
Page Break around Every Cell | Excel Discussion (Misc queries) | |||
cell size to page break | Excel Programming | |||
adding a new page break to an existing page break | Excel Discussion (Misc queries) |