ScreenUpdating = False fails in Excel 2013 and 2016

10
votes

Long-running, high-end Excel-based applications that I developed years ago and that run beautifully in Excel 2007 and 2010 look like Amateur Hour in Excel 2013 and 2016 because Application.ScreenUpdating = False no longer works reliably.

The screen unfreezes apparently when VBA code copies a preformatted worksheet from the macro workbook into a new workbook, although other circumstances must trigger it as well.

I’ve seen the threads on this subject that recommend “fiddling with the code” or “calling the code in a subroutine”. Unfortunately, I have to maintain hundreds of Excel applications each with thousands of lines of code and hundreds of users who are about to migrate to Office 2016, so rewriting is not an option. How can I recover Excel’s former elegance?

5
vbaexcel
FWIW, I found that DoEvents can lead to unexpected events being processed, which can lead to re-enabling ScreenUpdating. – Nickolay
Thanks, I'll have to look into that. I've always put a DoEvents in every loop, so changing programs won't be easy. – Weston E

5 Answers

2
votes

I wanted to leave a comment but I am not allowed to do so. Without a code sample it is very dificult to understand your problem (please see https://stackoverflow.com/help/how-to-ask and edit your question appropriately.

Here are some ideas: - Check if your code calls for code in a different procedure, maybe the Application.ScreenUpdating is turned on outside of the procedure. - Try this at the beginning of your procedure:

Application.Calculation = xlCalculationManual

Then, at the end of the code set it to:

Application.Calculation = xlCalculationAutomatic

It might help; however, without a code sample it is very difficult to properly help you.

1
votes

Here is a technique that helps reduce flickering and preserves the StatusBar message.

Application.Cursor = xlWait
Application.ScreenUpdating = False
. . .
Set wkbNewBook = Workbooks.Add
ThisWorkbook.Windows(1).Visible = False
. . .
ThisWorkbook.Windows(1).Visible = True
wkbNewBook.Activate
Application.ScreenUpdating = True
Application.Cursor = xlDefault
1
votes

We have been dealing with this problem now for a long time as my tools do show live animated charts which all of the sudden were completely static - we would have died for having at least a flickering animation.

Initially we tried to force the animation with a forced screenupdate but that did not work. Just by pure coincidence (copy pasted too many times) we stumbled into a solution which is equally unbelievable as it does seem to work. After each Application.ScreenUpdating = True we have added x3 times DoEvents. On some systems x2 times DoEvents works but x3 times does seem to be more reliable on the various office releases out there. Voila our animation came back :-)

Application.ScreenUpdating = True

DoEvents

DoEvents

DoEvents

We have not used it for the Application.ScreenUpdating = False statement but it might do some magic there. Anyway we hope that this road can help some of you finding creative functional solutions!

0
votes

After looking through many forums, I believe the flicker problem is related to SDI vs MDI. Someone suggested setting the application to not visible.

Application.Visible=False
enter code here
Application.Visible=True

This solved my flicker problem, but I didn't like how the excel application disappeared completely then suddenly reappeared for the user.

I was able to solve the issue to my liking by using a workaround this 'which window is on top' problem.

By leaving the main window alone, and forcing other workbooks to become not visible, letting the code run, then bringing them back to visible, it stopped flickering.

Application.Workbooks("yourworkbooktohide").Windows(1).Visible = False

Just remember to bring it back with =true.

Again, my script worked just fine in Excel 2010, but after "upgrading" to 2013, this flicker issue started.

0
votes

For me, only "Application.ScreenUpdating = False" did not completely cure the flickering. Calculation caused also the flickering. Adding "Application.Calculation = xlCalculationManual" solved the flickering issue. So, the code should be like:

Application.ScreenUpdating = False Application.Calculation = xlCalculationManual

... inportant code here....

Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic

Nico Mijnster.