Flickering of the Screen when the Macro runs.

[Neeraja]

Hi,

I have a Macro which runs for around 2 min. Two Excel
sheets are involved in this Macro. When the Macro runs,
the user sees the screen flickering and the focus keeps
shifting between the screens... all the while the macro
runs i.e for around 2 minutes. I also included a progress
bar in the macro, so the user sees the progress bar while
the macro runs, but the screen keeps flickering under
this progress bar all the while and this is very
disgusting to watch.
I want the focus to be on some blank screen (which does
not flicker) with the progress bar.
what do i do to achieve this. Could someone help me out
with this!!

Thanks in advance for any help.
Regards,
Neeraja.

 

[Rodg2000]

If you put Application.ScreenUpdating = False at the start of your macro
code, that should freeze the screen until the macro is completed. Not sure
what effect this will have on your progress bar, depending on where and how
it is displayed.

Rodg2000
(e-mail address removed)
(remove the _nospam to email)

 

[Rodg2000]

Here's a little more information for reporting the status/progress of an
executing macro. Go to this excellent link of various Excel VBA code
routines http://www.cpearson.com/excel.htm and download ProgressReporter
(about the 7th item down on the page I believe). I think it will do exactly
what you are looking for and coupling this with turning off the screen
updating should resolve your flicker/focus annoyance.

Rodg2000
(e-mail address removed)
(remove the _nospam to email)

 

[steve]

Neeraja,

Also - haven't seen your code but suspect that you might be using
".Select"
in your code.

Try to eliminate that as much as possible. This will speed up your code.
Range("A1").Select
Selection.Copy
can be replaced with
Range("A1").Copy
also
Sheets("Sheet1").Range("A1").Copy _
Destination:= Sheets("Sheet2").Range("A1")
will copy and paste without selecting.
Replace Sheet1 and Sheet2 with the sheet names, set the Range to what
ever you need.

Instead of a progress bar - I use
Application.StatusBar = "I am at Step 1"
place these after portions of your code (edit to suit). But be sure to end
with
Application.StatusBar=False
The messages will appear on the Status Bar at the bottom of the screen.