MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Progress Indicator

Posted by Simon McArdle on May 16, 2001 11:35 PM


Is it possible to have a progress indicator bar or something similar diplayed whilst a macro is completing to give the user the impression that something is actually happening.


Posted by Joe Was on May 17, 2001 5:56 AM

I use code like the listing below in most of my macros, that the user expects somthing to happen but may not know it happened. It turns on the assistant if it is not on, with sound and then makes the assistant do some activity. If this will do for you, look up msoAnimation in a macro help window, for the list of activities you can do. Some can be nested, but I found that this is limited, some activities seem to override others if nested one after another. So if you notice one animation is befor the main macro action and another after the main action, this works well. I too have experimented with code to put a moving bar or clock face in a user box. but have never been able to get it to work concurrently with the main action. Each time it runs before or after but not during the main action. hope this helps, JSW.

Sub Print_Inflation()
' This button prints the Inflation table from the Inflate sheet.
With Assistant
.On = True
.Visible = True
If Not Sounds Then Sounds = True
.Animation = msoAnimationBeginSpeaking
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
With Assistant
.On = True
.Visible = True
If Not Sounds Then Sounds = True
.Animation = msoAnimationPrinting
End With

End Sub

Posted by Simon McArdle on May 17, 2001 7:27 AM

Does this work with excel 97


Posted by Barrie Davidson on May 17, 2001 7:54 AM

Hi Simon, have a look at


Posted by Joe Was on May 17, 2001 11:16 AM

Animation(Assistant) Only Works in Excel 2000

Sorry, Simon code control of the assistant only works in Excel 2000. The next note will help if you have Excel 97. JSW

Posted by William Speareshaker on May 18, 2001 5:22 AM

As an alternative to the suggestions already made, it can also be done by a message on the status bar.

For example :-

Application.StatusBar = "Processing...."
Application.StatusBar = "Still Processing...."
Application.StatusBar = False

Or if you want to display the step by step progress of a loop :-

For c = 1 To x
Application.StatusBar = "Processing item " & c & " of " & x
Next c
Application.StatusBar = False

Bill S.