Create border at page break

[rhj]

For a formatted page with borders, if rows are added or deleted the
formatting is disturbed. How can border be automatically created for
the last row of the page when the rows are added or deleted?

 

[J.E. McGimpsey]

If you're only concerned about the way the sheet prints, one way
would be to run a Workbook_BeforePrint macro that added the bottom
border at print time, then removed it afterward:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wkSht As Worksheet
Dim hBreak As HPageBreak
Application.EnableEvents = False
For Each wkSht In ActiveWindow.SelectedSheets
If wkSht.Name = "bordersheet" Then
For Each hBreak In wkSht.HPageBreaks
With hBreak.Location.Offset( _
-1, 0).EntireRow.Borders(xlBottom)
.LineStyle = xlDouble
.Weight = xlThin
.ColorIndex = 5
End With
Next hBreak
wkSht.PrintOut preview:=True
For Each hBreak In wkSht.HPageBreaks
hBreak.Location.Offset(-1, _
0).EntireRow.Borders(xlBottom).LineStyle = _
xlNone
Next hBreak
Else
wkSht.PrintOut preview:=True
End If
Next wkSht
Application.EnableEvents = True
Cancel = True
End Sub

Put this in the ThisWorkbook code module.

 

[rhj]

Hi McGimpsey

Thank you for the code. It did not work as I had wanted. I was able to
find the file which has the required formatting. But it does not show
any macros in the workbook. I am attaching the file for your perusal.

Regards


Rahul

File Attached: http://www.exceltip.com/forum/attachment.php?postid=308141 (border_template.xls)