Mac – Can’t click on ActiveX control in Excel 2010 Standard

This may not be your answer, but it is a situation that I have seen that produces the results you've described.

Look in the macro(s) for the following statement:

Application.ScreenUpdating = False

This tells the Excel GUI interface to not show any changes/updates/anything that is going on. This can be desirable while a macro runs. However, you have to make sure the macro at some point sets this back to true like this:

Application.ScreenUpdating = True

If this is never set back to True, then it produces a situation like you've described.

First search the code for the 'ScreenUpdating' property being set, remove it, and see if that fixes the issue.

If you don't find 'ScreenUpdating' anywhere in the code, then you have some other problem.

Edit: Looks like someone already mentioned screen updating in the comments above.

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