Does anyone have any code, please, to help me out with selecting a
page in Page Break Preview.

I am always selecting pages so that I can run formatting macros
(borders etc) but I cannot work out how to programmatically do this.

Any help is much appreciated!

Regards,

Mark

Pages are not distinctly identified in Excel. You need to identify the
pagebreaks and determine the page layout yourself.

Here is some code I have posted previously that shows one way to get the
pagebreaks. You should be able to expand on that to determine where the
pages a

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



Regards,
Tom Ogilvy
"Mark Worthington" wrote in message
om...
Does anyone have any code, please, to help me out with selecting a
page in Page Break Preview.

I am always selecting pages so that I can run formatting macros
(borders etc) but I cannot work out how to programmatically do this.

Any help is much appreciated!

Regards,

Mark

Tom,

Many thanks, that'll give me something to think about! I've had an idea,
too, so I'll let you know how I get on ....

Regards,

Mark


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!