3

Excel 2010 only allows turning page breaks off or on for one worksheet at a time via File → Options → Advanced → "Display options for this Worksheet":

enter image description here

I previously came up with a VBA macro to toggle page breaks, but it only works on the sheet I have active:

Sub TogglePageBreaks()

    ActiveSheet.DisplayPageBreaks = Not ActiveSheet.DisplayPageBreaks

End Sub

The next logical question (which someone else had to point out to me) is how do I use a macro to toggle page breaks display for all worksheets in the active workbook?

Being new to VBA, I spent a couple hours researching how to loop through worksheets and also how the DisplayPageBreaks object works. I came up with an answer below.

0

2 Answers 2

5

Here's what I was able to come up with. I tested it successfully in Excel 2010. I'm very new to VBA so I struggled with this for a while. ws.Activate was the key, since DisplayPageBreaks only applies to the active sheet. Credit to RocketDonkey's post for making me realize that. Credit also to Rick Rothstein for the beautifully simple toggle code concept which I applied in my answer.


Sub ToggleWkBkPageBreaks()

    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets

        ws.Activate

        ActiveSheet.DisplayPageBreaks = True + False - ActiveSheet.DisplayPageBreaks

    Next ws

End Sub
2
  • @Oliver Salzburg - thank you very much for improving my question by adding the screenshot. I should have thought of that. Still learning forum best practices as I learn VBA!
    – AMM
    Commented Jun 7, 2013 at 20:27
  • That's fine, I'm happy to help. Thanks for contributing! :) Commented Jun 7, 2013 at 20:29
0

This should be more efficient than your example.
Deactivating screenupdating is a basic good practice when doing things that mess with the visuals (such as activating sheets). Also 2013 doesn't require sheet activation in order to toggle page breaks.

So... here you go:

Sub ToggleWkBkPageBreaks() ' start of public sub (private sub and function would not appear in macro menu in excel, would only be accessible to the code in the module)
    'Declaring variables
    Dim ws           As Worksheet 'worksheet object 
    Dim is2010OrLess As Boolean   'true or false variable (= type 'boolean')

    is2010OrLess = cint(Application.Version) > 15 ' check version (version "15.0" = excel 2013)
    Application.ScreenUpdating = False ' disable screen updating

    'do operations with ScreenUpdating turned off
    For Each ws In ThisWorkbook.Worksheets ' loop start (for each type, based on the sheet collection, hence the need for the ws object for the loop)
        If is2010OrLess = True then ws.Activate ' if version is less than exce3l2013, activate sheet. Else, don't activate the sheet (because it's unnecessary).
        ws.DisplayPageBreaks = not ws.DisplayPageBreaks ' .dysplayPagebreaks yelds a true or false so, we change it to ('=') the inverse (not true/not false)
    Next ws ' next sheet
    Application.ScreenUpdating = True ' Re-enable screen updating
End Sub

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .