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

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!


--
KellTainer
------------------------------------------------------------------------
KellTainer's Profile: http://www.excelforum.com/member.php...o&userid=34322
View this thread: http://www.excelforum.com/showthread...hreadid=542022

KellTainer wrote:
> 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

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