ScreenUpdating query

[jacko2401]

Hi

I am trying to speed up the execution of my code for a few userforms and make it as streamlined as possible. I have read up and seen in my own code that applying Application.ScreenUpdating = False at the start of the sub and Application.ScreenUpdating = True at the end (though it should turn off at the end of the sub procedure) speeds up VBA code because it is not interacting with the Excel interface when switched off.

I have 3 userforms that all have textboxes, listboxes and command buttons to perform specific functions such as initiate, change, *******, Keypress, afterupdate and terminate. I am unclear when to apply Application.ScreenUpdating = False and when to apply Application.ScreenUpdting = True at the most appropriate points as well as when not to apply this to userform/control event. Is it safe to put just on the initiate event when opening the userform, all userform events or in specific parts of the events that are performing specific commands. My code ranges from changing the userform control properties by cycling through each form control as well as working with worksheets, ranges and arrays. A specific points values are copied to the worksheet from the userform textboxes and vice versa depending on the task being undertaken and I find that this is a lot quicker with screenupdating turned off.

Any help will be much appreciated.

Thanks
Steve

 

[BrianMH]

I would only turn off screen updating when actually updating spreadsheets and it really only makes much difference when your working with lots of cells. It really depends on what you are doing as to whether it adds efficiency. Also make sure you add some error handling and turn it back on in case of error. Here is an example.

Sub SubWithErrHandling()
On Error GoTo ErrHandler
Application.ScreenUpdating = False
'do some stuff


'create exiter so that you always turn it back on.
'this can also be used to always turn calculations back on or any other things you turn off


Exiter:
Application.ScreenUpdating = True
'exit sub stops the code from running past this point
Exit Sub


ErrHandler:
'alert of the error
MsgBox Err.num & " - " & Err.Description
'move back to exiter to turn screenupdating back on.
GoTo Exiter




End Sub