running code without seeing the various steps

[sedgely]

Hi
i have created an Excel work book with code that does several formatting and data manipulation routines on different sheets. All works well apart from the fact that the users can see everything that happens. ie the sheets becoming active, the ranges being selected etc.
Is there a way of making it so that this is all performed "in the background" so the user just sees the result?

Cheers
Craig

 

[TonyJollans]

Hi sedgely,

Code ..

Application.ScreenUpdating = False

.. before you start your stuff and ..

Application.ScreenUpdating = True

.. after you have finished.

Enjoy,
Tony

 

[sedgely]

Thanx Tony
That worked a treat, i knew there'd be a simple solution

Craig

 

[sedgely]

Hi
now that i've got that working i thought it might be nice if the user could see some sort of indicator that something is happening besides the hourglass cursor, can i put up a message just while the code is running, then replace with another message when the code has finished?

Cheers
Craig

 

[TonyJollans]

Hi sedgely,

The simplest way is to put a message in the Status bar ..

Application.DisplayStatusBar = True
Application.StatusBar = "Please wait while I do some stuff..."

.. and, at the end ..

Application.DisplayStatusBar = False

 ' If you want

Application.StatusBar = False

But it's not very obvious to the User. For something more obvious you really need to set up a UserForm. Whichever way you choose, you can change the message as often as you like to keep the User informed of progress.

Enjoy,
Tony

 

[RamziSaab]

look at this link, looks much better then excel default status bar#

http://j-walk.com/ss/excel/tips/tip34.htm

if you need help using it, tell me and i will try to help you

 

[combo]

You can also use status bar as a progress bar, works also when ScreenUpdating=False:

[tt]Application.ScreenUpdating = False
For i = 1 To MaxValue
Application.StatusBar = String(i * 30 / MaxValue, Chr(127))
' do something
Next i
Application.StatusBar = False
Application.ScreenUpdating = True[/tt]

And with API you can change colour, font and size.

combo

 

[sedgely]

Thanks all
decided to go with the straightforward status bar option for now as it is the quickest, but when i get time i will look into the option suggested by RamziSaab as it looks quite neat.

Thanks again
Craig