This often leads a user to believe they have to End Task on Excel when all they needed to do was wait a few more seconds. Also, all events seem to stop after that too.
Also, after a 'focus steal', any code that momentarily sets ScreenUpdating to True, and then back to False again to force a re-draw, doesn't happen.
Windows brings the Excel window 'back to top' even though it's too busy to interact, and takes the user away from the app that IS responding, back to an app that it then thinks is 'Not Responding' (when it is, but it's just busy). Of course, I set an OnTime to showPointer as part of my hidePointer routine.Īnother thing hiding the pointer stops is the dreaded 'focus stealing' phenomenon during which, when a user starts a process in Excel, but decides to check their Outlook while Excel is busy, then what Excel tends to do (and all Windows apps do this to some extent - MS Office is the worst though) is 'steal focus' - i.e. But with hidePointer, the problem is solved - no rogue events can be triggered, and if an error occurs it's still possible to interact (albeit a little blindly) enough to get back to the VBE and see what's gone wrong (and doing a showPointer in the process). Setting Application.Interaction to False is too risky because if an error ever occurs before it's set back to True again at the end of the procedure you're stuffed. Setting EnableEvents to False is no good because I still want non-click-invoked Events to occur, but I don't want any click-invoked events to occur.
My project uses a lot of Event procedures, and any process that takes a long time uses DoEvents a lot to allow progress bars (I even have a spinning progress 'whirly arrow' which appears if entries from a database are taking a while to appear) presents the opportunity for an event to be triggered by the user clicking somewhere where they shouldn't while the update is going on. Having tried it, I was proved right, and has sped up my main project by about 3 times.
This is a type of Screen Updating that the ScreenUpdating property doesn't touch.įor months I have figured that hiding the pointer during updates would speed up the process much more than turning ScreenUpdating off does. Even with a fixed cursor, it still flickers on/off wildly if a lot is happening.
So, with ScreenUpdating set to False, if you manipulate or create a lot of screen objects (Autoshapes, Comments, etc.) then the process is very much slowed down (and also presents the user with a glitchy, messy looking visual feedback) by the mouse pointer continually changing between 'arrow only' and 'arrow with hourglass'.
However what ScreenUpdating doesn't stop is Excel's context-sensitive pointer changing, and even setting it to a fixed value doesn't stop it redrawing. There are actually very good and valid reasons for doing this rather than changing the cursor shown.įor instance - when changing a lot of cell values and formats, the advice to turn off ScreenUpdating is well-known and has proven benefit. I've looked for a solution to this a number of times (sporadically, not desparately), and only on my last search did I find this. believe me, this is a very useful piece of code.