Microsoft Excel 2013 ScreenUpdating fails

2
votes

After running a macro for a prolonged period of time the ScreenUpdating in Excel totally stops working even when the macro finishes. I never set it to false but it behaves as if it is set to false and the only way to make Excel update the screen again is to close it out and reopen it. The VBA screen works however. I have tried to run DoEvents and ScreenUpdating=True but the Excel window does not update visually (it is not frozen). Is there anyway of clearing the ScreenUpdating buffer? I have tried manually setting it to false and true to no avail.

If you want to duplicate this error use this code

Application.OnTime (Now + TimeValue("0:00:05")), "macroName", (Now + TimeValue("0:00:10")), True

then I have the code copy and paste some values and I control when the loop stops with a countdown value in cell A1 for example.

1
vbaexcel
@PortlandRunner View -> Immediate Window (Ctrl+G), this window pops up even when it is not updating the screen and I can tell invisibly I am interacting with the Excel Sheet if I start clicking on the background but it behaves exactly as if ScreenUpdating is set to false. In the immediate window I try Application.ScreenUpdating=False and then True and nothing changes. It is as if I blew up the ScreenBuffer from all my copy and pasting. The problem is it happens nearly every time (it is pretty random too!). – CodeCamper
I've never seen this, an independent sub with ScreenUpdating=True has always worked for me. – n8.
@n8 The thing is I am not even turning off ScreenUpdating in my Macro at all but it is behaving as if it is off. There must be some sort of screen buffer overflow. When I change windows too much it will also do this. I wish there was some way of clearing the screen buffer. – CodeCamper
Yeah, I get it. I wonder if you can send the data without changing screens. I like to write data without changing sheets, that kind of approach may help you. Like Sheets(1).Cells(1,1) = Cells(1,1) instead of Cells(1,1).Copy, Sheets(1).Activate, Cells(1,1).Paste – n8.
@n8 that is currently how I am doing it, it can also be that I have too many cells updating every second. But I wish there was some VBA way of fixing this screen overflow or whatever it is. – CodeCamper

1 Answers

2
votes

I got this issue in my Excel 2013. For me, it was caused when I loaded a userform. It was very, very strange, however. After a lot of trial and error I found that what caused it was using a Sheets("MySheet").Activate command. I moved this command from running after I had shown and closed the form, to immeiately before I show the form (i.e., immediately when user clicks the 'show form' button.) This fixed it. Try fiddling with your sheets.activate commands.