Bottom border on Excel pages (Add and Remove)

[Un-Do Re-Do]

I have this code which adds a border to every page break. This will be the bottom border for a multi-page Excel sheet.
When I edit the document though by adding or subtracting rows, the border will not coincide with the page breaks.
I want to have something which first removes all the borders which span columns A-I (9 columns). There will be other regions with borders but these will not fully span columns A-I, so leave these as they are.

Please Login or Register  to view this content.

[Un-Do Re-Do]

Alternatively, can the following events be called up at once.
1. Add borders before printing: Sub page_break_border()
2. Print.
3. Remove border after printing: Sub page_break_border_undo()

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[mikerickson]

Have you considered using footers or the "repeat this row every page" feature to put a border at the bottom of each printed page?

[Un-Do Re-Do]

@mikerickson
I have considered a footer. Using an image was the best choice ... until I found out that footers have a limited width. Using underscores was not really an option for me.
With "repeat rows at every page", I have used this for the top of pages where I need a couple of rows on every page.

[mikerickson]

My bad. The repeat rows feature only works at the top.

[Un-Do Re-Do]

This seems to work.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Un-Do Re-Do]

A funny thing seems to happen, with Sub page_break_border_undo().
Depending on the zoom %, it may or may not remove the border on the page break.
If I zoom out to 10%, it removes all border on page breaks. If I zoom in to 100%, it removes border on first page break only.
Why does this happen?

Note: This applies in Normal View. When in Page Break Preview, the problem goes away.

[humdingaling]

thats strange
are you referring to Zoom or scaling?
because Zoom doesnt change the page breaks

Please Login or Register  to view this content.

^what happens here?

[Un-Do Re-Do]

@humdingaling
I am referring to Zoom level.
I don't expect it to occur either. Later on I found that problem goes away when in Page Break Preview.
The sub in post #8 brings up the print dialog. So first add border, then print, then remove border. Remove is required because document is edited and printed frequently.

[humdingaling]

sorry let me rephrase
what do you do when the print dialog comes up
if you just click print....so you need the dialog at all?

if you play with any settings that may be something that inadvertently changes the pagebreaks which is why it doesnt remove the borders

to be sure ..if your testing you can add a debug line to capture the ranges

Please Login or Register  to view this content.

Put this in the loop in both the borders and undo macro to see if you get exact same ranges twice

[Un-Do Re-Do]

@humdingaling
You're right I think, it may have something to do with the print macro.
I didn't know how to test with "Debug.print pagebottom.location.address".
I changed from "Application.Dialogs(xlDialogPrint).Show" to "ActiveSheet.PrintOut" and this seems to work.

Edit - no it doesn't work again.

Edit 2 - tried this patch, hopefully it works.
"ActiveWindow.View = xlPageBreakPreview" at beginning of "Sub page_break_border()"
"ActiveWindow.View = xlNormalView" at end of "Sub page_break_border_undo()"

[humdingaling]

Please Login or Register  to view this content.

using an array to capture your actual rows your page breaks occur
^this method will ensure what you insert as borders is exactly what you take away

i added the activewindow.view code in because it didnt assign pb properly without it

[Un-Do Re-Do]

Thank you very much, works perfectly.