Stopping screen flickering (jumping worksheets) in Excel as VBA execs? 2

[VBAguy22]

Hi
I have a button that runs a macro that copies some stuff between the sheets in the workbook.
It takes a bit of time (about 40 secs) and the screen keeps changes from sheet to sheet and gives me headache (leave alone looks unprofessional)
Is there a way to just stay on the menu sheet, display the hourglass and let the process execute in the background without the sheet jumping?
THX!

 

[SkipVought]

Hi,

FYI

How Can I Make My Code Run Faster? faq707-4105

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[xlbo]

some useful members of the APPLICATION object

Application.Screenupdating = false / True
.Calculation = xlcalculationmanual / xlcalculationautomatic
.EnableEvents = True / False
.Statusbar = "Test text on status bar" / false
.DisplayAlerts = True / False
.DisplayScrollBars = True / False
.EnableCancelKey = True / False


and there are loads more - top tip - the F2 key will open the object model in the VBE. Once in there you can search for an object and see all it's properties, methods and functions

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[VBAguy22]

Hm yeah I did that
I think the reason it's still doing it is because I open another workbook in my VBA code...I guess I should put ScreenUpdating = False after every line where I open a new workbook?

 

[SkipVought]

If you put ScreenUpdating at the START & END of your MAIN and no where else, should work.

Also MINIMIZE Select & Activate method usage.

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[PHV]

Why not playing with the Visible property of the Window object ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[xlbo]

PHV - 'cos if you get a little error, you get the lovely situation where Excel exists as a process but you can't see / use it !!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question