Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
(I don't have Excel 2010) Can you not group the sheets and click on something like Page Break Preview? –  pnuts Jun 7 '13 at 21:41

1 Answer 1

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
share|improve this answer
    
@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 Jun 7 '13 at 20:27
    
That's fine, I'm happy to help. Thanks for contributing! :) –  Oliver Salzburg Jun 7 '13 at 20:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.