Screen off during Macro execution

  • Thread starter Thread starter CLR
  • Start date Start date
C

CLR

Hi All......

With you fine folks help, I've managed to put together a nifty macro that
does what I want it to do. The only thing now is, during it's executon the
macro jumps back and forth between screens and this causes each one to flash
up on the monitor and I would prefer that they not do that.........any way
to "turn off the display at the beginning of the macro and turn it back on
again at the end"......??......or better yet, to put up some "Please wait"
message during processing.......

TIA
Vaya con Dios,
Chuck, CABGx3
 
CRoberts,

To stop updating of the screen :
Application.Screenupdating = False

To start it again :
Application.Screenupdating = True

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Vaya, use this to cut off screen updating
Application.ScreenUpdating = False
'your code here
Application.ScreenUpdating = True


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **
 
Chuck,

I don't think the jumping back and forth between screens problem
can be fixed if you're activating or selecting them within your macro.
Application.ScreenUpdating = False seems to reset when you
switch worksheets.
You already knew all that though.

What I like to do is an idea that I got from Tom Ogilvy.
Create a UserForm and if you want, maximize it to fill the screen.
Run all of your macro code from within the Activate event of the
UserForm.

Private Sub UserForm_Activate()
DoEvents
' your code here
Unload UserForm1
End Sub

If you're feeling brave, you could use Application.Visible
to shut off the background altogether while the UserForm
is displayed (just pray that you don't encounter an error
between the Application.Visible = False and True statements)

John
 
Hey Chuck,

Merry Christmas/Happy New Year...

You can use the following to calm the screen display:

Application.ScreenUpdating = False

then before exiting your sub use:

Application.ScreenUpdating = True

Be aware that showing a message box while ScreenUpdating is turned off may
confuse your users if they move/drag the message box.

Regards,
Jim Cone
San Francisco, CA
D582
 
You could put the please wait in the status bar like this, or to put a
message on the screen have a look at "please_wait.zip" here
http://www.xl-logic.com/pages/vba.html

Application.ScreenUpdating = False
Application.StatusBar = "File Updating, Please Wait....!"
'your code here
Application.StatusBar = ""
Application.ScreenUpdating = True


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **
 
Application.ScreenUpdating = False

Nifty macro code

Application.ScreenUpdating = True

However, it would be better to design your code so it doesn't do selecting
and activating so you would not have this problem.
 
John,

Is that so ? I've never noticed that. I'm (now) using XP and have tested
the following testprogrogram in which I select several sheets.
I do however not see any flickering of sheets. Maybe this is solved in XP
?
(I will try it on a '97 version on monday, but I'm curious wether you know
more of it)


I've tested with this and with several smaller values of Atst

Sub AA()
Atst = 22500000
Application.ScreenUpdating = False
Sheets(2).Select
B = 0
For I = 1 To Atst
B = B + I
Next
Sheets(3).Select
B = 0
For I = 1 To Atst
B = B + I
Next
Sheets(1).Select
B = 0
For I = 1 To Atst
B = B + I
Next
Application.ScreenUpdating = False
End Sub

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
CLR said:
Hi All......

With you fine folks help, I've managed to put together a nifty macro that
does what I want it to do. The only thing now is, during it's executon the
macro jumps back and forth between screens and this causes each one to flash
up on the monitor and I would prefer that they not do that.........any way
to "turn off the display at the beginning of the macro and turn it back on
again at the end"......??......or better yet, to put up some "Please wait"
message during processing.......

TIA
Vaya con Dios,
Chuck, CABGx3
Click to expand...

Yes. bracket your code with:

application.screenupdating = false
..
..
..
application.screenupdating = true
 
Auk Ales,

As to why screen updating doesn't always work, I'm
not exactly sure. I do know that it sometimes doesn't when
selecting different sheets and that many others (including me)
have run into this problem.
I tried your code in Excel 2000 and it didn't flicker at all???

As for Chuck's question, knowing that he's a regular contributor
to the ng's, I was sure he had already tried the ScreenUpdating
so I wanted to offer some other alternatives.

John
 
Many thanks to all who responded.

I guess that's what makes these newsgroups so wonderful, is that you can
ask a simple question, and get back several different approaches to the
problem. Then you can choose the one that is the easiest for you to
understand and implement. Indeed, Auk Ales first response worked just fine
for me in my Win98SE-XL2k situation. Thanks for the compliment John, but
I'm sorry to disappoint you. I was not familiar with it, as I'm just
getting in to this code stuff.
I'm still at the "editing recorded macros, and copying other peeps code"
stage. <g>

I will later look in to the other comments and suggestions......they will
certainly help my learning process.

Merry Christmas to everyone........

Vaya con Dios,
Chuck, CABGx3
 
John,

Thanks for your response. From this I get the impression that my code
(which indeed doesn't flicker at all on my computer) DID do that on yours.
Just for the sake of intrest i will (as i already said) try it on the
computer at the office (Windows NT ; Excel 97).
I assume I'm just lucky that I (up to now) have never run into this, but
it's always good to know about it (just in case I do at some time run in it
and then save me a lot of time fibding out what is going on).

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Auk Ales,
DID do that on yours
Click to expand...
No, it didn't flicker at all on mine (XL2000) and that surprised me
as I expected it to flicker.
I did some searching but can't find a definitive list of what turns
Application.ScreenUpdating back to True but it does happen.
It happens on some of the workbooks that I've created and
based on the fact that there are thousands of threads in Google
on the subject of it not working as expected there are obviously
other things that can set it back to True unexpectedly.
I do know that it's a little more volatile than some of the other
settings in Excel to prevent the application from locking up.
Example...when you set EnableEvents, it'll hold it's setting
regardless of whatever else happens. ScreenUpdating will revert
to True when your code completes or when control is passed back
to the workbook.

Anyway, you've piqued my interest in finding out more about it and
I intend to find out just what it is in my own workbooks that sets
it back to True when I don't want it to.

I'll post back if I find anything.

If anyone else can shed some light on what events, etc. can set
it back to True after it's already been set to False, please feel
free to chime in.

John
 
CLR said:
Many thanks to all who responded.

I guess that's what makes these newsgroups so wonderful, is that you can
ask a simple question, and get back several different approaches to the
problem. Then you can choose the one that is the easiest for you to
understand and implement. Indeed, Auk Ales first response worked just fine
for me in my Win98SE-XL2k situation. Thanks for the compliment John, but
I'm sorry to disappoint you. I was not familiar with it, as I'm just
getting in to this code stuff.
I'm still at the "editing recorded macros, and copying other peeps code"
stage. <g>

I will later look in to the other comments and suggestions......they will
certainly help my learning process.

Merry Christmas to everyone........

Vaya con Dios,
Chuck, CABGx3
Click to expand...
 
Back
Top