How can you turn off screen updates when running a macro in Excel

A

Alex

Hi all,

Third (and probably last post) for today. Thanks to the people that read and
answered my questions.

In Excel, I am running several Macro that opens other Excel files, copies
data, then closes the file(s).
The screen is jumping all over.

Is there a way to stop the screen update while the macro is running?

Thanks for our help

Alex
 
K

KellTainer

Hi, there is a screenupdating property in the application object. Just
set it to false whenever u have any code that will update the screen.
Just one warning though, the property will automatically reset to true
once the routine is ended, so you have to set the property in any
methods which you are using.

Sub YourMethod()
Application.ScreenUpdating = False

'Your code comes in here

Application.ScreenUpdating = True
End Sub

Kudos!
 
P

Puppet_Sock

KellTainer said:
Hi, there is a screenupdating property in the application object. Just
set it to false whenever u have any code that will update the screen.
Just one warning though, the property will automatically reset to true
once the routine is ended, so you have to set the property in any
methods which you are using.

Sub YourMethod()
Application.ScreenUpdating = False

'Your code comes in here

Application.ScreenUpdating = True
End Sub
Click to expand...

If your app starts getting lots of VBA in it, a trick I find useful is
a
utility module with things like this there.

So, you could have a sub like so:

public sub SetScreenUpdating(su as boolean)
Application.ScreenUpdating = su
end sub

Then you can turn it off and on and not have to remember what
object it goes in, what the sub member is, etc. You can do the
same with calculation. If you are locking/unlocking sheets and
cells, you can do the same. And you can put little comments
by the sub indicating what they are for, when to use them, etc.

Eventually I settled on a format like so:

User <-> Access Layer Module <-> VBA to do the Work

Whenever the user types anything, clicks any button, etc.,
code in the Access Layer Module gets it first. It unlocks stuff,
turns off calcs or updating if required, and just generally gets
things ready for the task. Then it calls other modules to do
the actual work. When the working code finishes, it returns
control to the access layer. The access layer then puts things
back the way they need to be in order to let the user keep
working.
Socks
 
Top