Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
determine pagebreak using VBA
How can I, using VBA, determine where a pagebreak takes place?
....if it is posible. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
determine pagebreak using VBA
You will probably need to loop through the cells in a range (sheet) and
perform a check on each cell: If Activecell.EntireRow.PageBreak < -4142 then MsgBox "Break above this cell" End If Hope this helps Rowan "mirec" wrote: How can I, using VBA, determine where a pagebreak takes place? ...if it is posible. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
determine pagebreak using VBA
Here is a method to get an array of horizontal pagebreaks and vertical
pagebreaks. The horizontal pagebreaks are a list of rows that have the pagebreak and vertical a list of column numbers: Sub Tester1() Dim horzpbArray() Dim verpbArray() ThisWorkbook.Names.Add Name:="hzPB", _ RefersToR1C1:="=GET.DOCUMENT(64,""Sheet1"")" ThisWorkbook.Names.Add Name:="vPB", _ RefersToR1C1:="=GET.DOCUMENT(65,""Sheet1"")" i = 1 While Not IsError(Evaluate("Index(hzPB," & i & ")")) ReDim Preserve horzpbArray(1 To i) horzpbArray(i) = Evaluate("Index(hzPB," & i & ")") i = i + 1 Wend ReDim Preserve horzpbArray(1 To i - 1) Debug.Print "Horizontal Pagebreaks (rows):" For J = LBound(horzpbArray, 1) To UBound(horzpbArray, 1) Debug.Print J, horzpbArray(J) Next J i = 1 While Not IsError(Evaluate("Index(vPB," & i & ")")) ReDim Preserve verpbArray(1 To i) verpbArray(i) = Evaluate("Index(vPB," & i & ")") i = i + 1 Wend ReDim Preserve verpbArray(1 To i - 1) Debug.Print "Vertical Pagebreaks (columns):" For J = LBound(verpbArray, 1) To UBound(verpbArray, 1) Debug.Print J, verpbArray(J) Next J End Sub This uses an Excel 4 macro to get this information. This is much faster than the VBA pagebreak which uses the printer driver and can be very slow. The is a pagebreak property of the range. It can be tested to see if a pagebreak exists if rows(6).pagebreak = xlNone then 'No pagebreak Else ' Has pagebreak if rows(6).pagebreak = xlPageBreakAutomatic then 'Automatic pagebreak elseif rows(6).pagebreak = xlPageBreakManual then ' Manual pagebreak End if End if Combining the above gives: Sub Tester1() Dim horzpbArray() Dim verpbArray() Dim brkType As String ThisWorkbook.Names.Add Name:="hzPB", _ RefersToR1C1:="=GET.DOCUMENT(64,""Sheet1"")" ThisWorkbook.Names.Add Name:="vPB", _ RefersToR1C1:="=GET.DOCUMENT(65,""Sheet1"")" i = 1 While Not IsError(Evaluate("Index(hzPB," & i & ")")) ReDim Preserve horzpbArray(1 To i) horzpbArray(i) = Evaluate("Index(hzPB," & i & ")") i = i + 1 Wend ReDim Preserve horzpbArray(1 To i - 1) Debug.Print "Horizontal Pagebreaks (rows):" For j = LBound(horzpbArray, 1) To UBound(horzpbArray, 1) If Rows(horzpbArray(j)).PageBreak = xlNone Then brkType = "None" Else ' Has pagebreak If Rows(horzpbArray(j)).PageBreak = xlPageBreakAutomatic Then brkType = "Automatic" ElseIf Rows(horzpbArray(j)).PageBreak = xlPageBreakManual Then brkType = "Manual" Else brkType = "Unknown" End If End If Debug.Print j, horzpbArray(j), brkType Next j i = 1 While Not IsError(Evaluate("Index(vPB," & i & ")")) ReDim Preserve verpbArray(1 To i) verpbArray(i) = Evaluate("Index(vPB," & i & ")") i = i + 1 Wend ReDim Preserve verpbArray(1 To i - 1) Debug.Print "Vertical Pagebreaks (columns):" For j = LBound(verpbArray, 1) To UBound(verpbArray, 1) If Columns(verpbArray(j)).PageBreak = xlNone Then brkType = "None" Else ' Has pagebreak If Columns(verpbArray(j)).PageBreak = xlPageBreakAutomatic Then brkType = "Automatic" ElseIf Columns(verpbArray(j)).PageBreak = xlPageBreakManual Then brkType = "Manual" Else brkType = "Unknown" End If End If Debug.Print j, verpbArray(j), brkType Next j End Sub Sample Output: Horizontal Pagebreaks (rows): 1 13 Manual 2 24 Manual 3 39 Manual 4 67 Manual 5 87 Manual 6 114 Automatic Vertical Pagebreaks (columns): 1 2 Manual 2 6 Automatic This should get you started. Regards, Tom Ogilvy "mirec" wrote in message ... How can I, using VBA, determine where a pagebreak takes place? ...if it is posible. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cant move pagebreak in PageBreak View in Excel2003 | Excel Worksheet Functions | |||
PageBreak-broke? | Excel Programming | |||
PageBreak Problems | Excel Programming | |||
How to pagebreak | Excel Programming | |||
PAGEBREAK-MACRO | Excel Programming |