Getting existing page breaks in VBA

[Haighstrom]

I need to write a general function to set a border around each printed page, given a set of page breaks.

The user will set his/her own print areas, and then the macro is required to format a border around these edges (exactly where the blue lines appear in page break preview.

How do I access the page break data in Excel? From here, I imagine it is pretty easy, as I just .Select the pages in turn and apply the relevant formatting, but this first step has me stumped.

Thanks
Haighstrom

 

[Andrew Poulsom]

Welcome to MrExcel.

A Worksheet has HPageBreaks and VPageBreaks Collection Objects, each of which has a Range property.

 

[Haighstrom]

Thanks Andrew. I have written my macro which works almost as intended, but I have a slight bug which is that the formatting of the border is not working. The border is coming out as a dotted orange line... Please advise!

I was hoping that the line "Selection.BorderAround , xlContinuous, xlThick, xlColorIndexAutomatic" would give me a thick black line???

Sub AddPageBorders()
Dim W As Worksheet
Dim Page As Integer
Set W = ActiveSheet
Range(Cells(1, 1), Cells(W.HPageBreaks(1).Location.Row - 1, 9)).Select
Selection.BorderAround , xlContinuous, xlThick, xlColorIndexAutomatic
For Page = 1 To W.HPageBreaks.Count - 1
Range(Cells(W.HPageBreaks(Page).Location.Row, 1), Cells(W.HPageBreaks(Page + 1).Location.Row - 1, 9)).Select
Selection.BorderAround , xlContinuous, xlThick, xlColorIndexAutomatic
Next Page
Range(Cells(W.HPageBreaks(W.HPageBreaks.Count).Location.Row, 1), Cells(Range("A65536").End(xlUp).Row, 9)).Select
Selection.BorderAround , xlContinuous, xlThick, xlColorIndexAutomatic

End Sub

 

[shg]

You've got an errant comma in there:

Selection.BorderAround [COLOR=red][B], [/B][/COLOR]xlContinuous, xlThick, xlColorIndexAutomatic

Better to pass arguments by name.

 

[Haighstrom]

Thanks. It was throwing errors when I put the arguments in brackets.

All working now, cheers guys.

The working macro was as follows:

[COLOR=#1f497d][FONT=Calibri]Sub AddPageBorders()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>[/FONT][/COLOR]
[COLOR=#1f497d][FONT=Calibri]<o:p> </o:p>[/FONT][/COLOR]
[COLOR=#1f497d][FONT=Calibri]Dim W As Worksheet<o:p></o:p>[/FONT][/COLOR]
[COLOR=#1f497d][FONT=Calibri]Dim Page As Integer<o:p></o:p>[/FONT][/COLOR]
[COLOR=#1f497d][FONT=Calibri]<o:p> </o:p>[/FONT][/COLOR]
[COLOR=#1f497d][FONT=Calibri]Set W = ActiveSheet 'Make typing easier :)<o:p></o:p>[/FONT][/COLOR]
[COLOR=#1f497d][FONT=Calibri]<o:p> </o:p>[/FONT][/COLOR]
[COLOR=#1f497d][FONT=Calibri]'First Page<o:p></o:p>[/FONT][/COLOR]
[COLOR=#1f497d][FONT=Calibri]Range(Cells(1, 1), Cells(W.HPageBreaks(1).Location.Row - 1, W.VPageBreaks(1).Location.Column - 1)).Select<o:p></o:p>[/FONT][/COLOR]
[COLOR=#1f497d][FONT=Calibri]Selection.BorderAround xlContinuous, xlThin, xlColorIndexAutomatic<o:p></o:p>[/FONT][/COLOR]
[COLOR=#1f497d][FONT=Calibri]<o:p> </o:p>[/FONT][/COLOR]
[COLOR=#1f497d][FONT=Calibri]'Middle Pages<o:p></o:p>[/FONT][/COLOR]
[COLOR=#1f497d][FONT=Calibri]For Page = 1 To W.HPageBreaks.Count - 1<o:p></o:p>[/FONT][/COLOR]
[COLOR=#1f497d][FONT=Calibri]    Range(Cells(W.HPageBreaks(Page).Location.Row, 1), Cells(W.HPageBreaks(Page + 1).Location.Row - 1, W.VPageBreaks(1).Location.Column - 1)).Select<o:p></o:p>[/FONT][/COLOR]
[COLOR=#1f497d][FONT=Calibri]    Selection.BorderAround xlContinuous, xlThin, xlColorIndexAutomatic<o:p></o:p>[/FONT][/COLOR]
[COLOR=#1f497d][FONT=Calibri]Next Page<o:p></o:p>[/FONT][/COLOR]
[COLOR=#1f497d][FONT=Calibri]<o:p> </o:p>[/FONT][/COLOR]
[COLOR=#1f497d][FONT=Calibri]'Last Page<o:p></o:p>[/FONT][/COLOR]
[COLOR=#1f497d][FONT=Calibri]Range(Cells(W.HPageBreaks(W.HPageBreaks.Count).Location.Row, 1), Cells(Range("A65536").End(xlUp).Row, W.VPageBreaks(1).Location.Column - 1)).Select<o:p></o:p>[/FONT][/COLOR]
[COLOR=#1f497d][FONT=Calibri]Selection.BorderAround xlContinuous, xlThin, xlColorIndexAutomatic<o:p></o:p>[/FONT][/COLOR]
[COLOR=#1f497d][FONT=Calibri]<o:p> </o:p>[/FONT][/COLOR]
[COLOR=#1f497d][FONT=Calibri]End Sub<o:p></o:p>[/FONT][/COLOR]