Making a macro run smoothly.. 1

[basepointdesignz]

Hi,

Does anyone know how to make a macro run smoothly and without displaying everything it does?

For example, I have a macro on an Excel worksheet and what it does is:

1: Select a pre-defined range.
2: Clear the contents of the range.
3: Change the formatting of the range.
4: Selects a random cell to deselect the range.

The trouble is, evertime the macro is run (from a command button on screen), the user can see the range being selected (turning blue) and everything else seems to happen in jerky movements.

My question is, is there a way to stop the screen flickering like that, so from the users point of view, the see nothing but the contents of the range disappearing?

Any ideas,

Cheers,

Renegade.. BULLET-PROOF DESiGNZ
renegade@tiscali.co.uk
+447919 117062 (mobile)

 

[Nikita6003]

Sure - look thru' the forum & you'll get loads of examples. Here's a little routine I run when starting a macro:

With Application
  'Switch off screen updating so  macro runs smoothly
  .ScreenUpdating = False

  'Cursor to hourglass
  .Cursor = xlWait

  'Switch off Excel's automatic question dialog (e.g. 'Would you like to Save Changes'
  .DisplayAlerts = False

  'Message on statusbar so user won't restart everything because he/she thinks excel's stuck
  .Statusbar = "Please wait ... running update Excel
End With

You'll have to run the following to restore Excel to default:
With Application
.ScreenUpdating = True

.Cursor = xlDefault

.DisplayAlerts = True

.Statusbar = False
End With[/code]

HTH

Cheers
Nikki

PS - I've got these as separate routines on a separate module called modGeneral which I import into all xl apps I make. Handy if you need stuff like this on a regular basis ...