MrExcel Publishing
Your One Stop for Excel Tips & Solutions

progressbar on a userform

Posted by amroo on May 28, 2001 7:07 AM

Bonjour, I have a macro (MacroImport) that imports data on a spreadsheet and execute differents operations on the data.
When I click on a button import file I choose the file, so when I click on start it runs, I hide my worksheet with the code: Application.ScreenUpdating = False. A userform it shows and I want a progressbar informs the user about the % of work?
Who can help me on this

Posted by Dax on May 28, 2001 7:34 AM

Bonjour Amroo,

You can make a fairly decent progress indicator by doing something like this: -

On your userform add two labels each with a different back colour. Set one of the labels width to 0 and make sure it is the same height and has the same left coordinate as the other one. As your macro is progressing you can update the progress bar with code like this: -

Sub UpdateProgressIndicator(NewPercent As Integer)
lblProgress.Width = NewPercent / 100 * lblTotal.Width
End Sub

where lblProgress is the name of the label that you set to width 0.

I hope this is clear,

Posted by Dave Hawley on May 28, 2001 7:37 AM

Hi Amroo

Firstly the code:
Wont hide a Worksheet. It just stops the screen from what is known as 'Repainting'. To hide a Worksheet use:
Sheet1.Visible = xlSheetHidden


Sheet1.Visible = xlSheetVeryHidden


Sheet1.Visible = False

Secondly, if you have turned off Screenupdating any 'Progess bar' will not update either. But this is a much better option as it will speed up the maco execution. While a Progress bar will actually slow down your code.

A better opyion IMO is to put some brief text in the statusbar, eg;
Application.StatusBar = "Macro running, please wait....."

Then on completion you would use:

Application.StatusBar = ""

The other problem is that unless you are using Excel 2000 your code will stop the moment the UserForm shows. If you are using Excel 2000 you would need to set it's "ShowModal" property to True. Progress bars are really generally only used for vey lengthy Loops. This because each Loop can move the meter a bit more.


OzGrid Business Applications

Posted by Dave Hawley on May 28, 2001 8:09 AM

Amroo, here is a link to: a "Progress Indicator" download example:

DaveOzGrid Business Applications

Posted by Dax on May 28, 2001 8:19 AM

A few comments...

Yes, using this type of progress indicator will mean that the code runs slower than if updating the statusbar. I suppose it's a question of aesthetics at the expense of some loss in performance. If the indicator was being updated thousands and thousands of times then this would be noticeable but if it was being updated 1000 times during the macro for example, then you'd lose only a couple of seconds.

Turning off screen updating only affects the Excel window, not any user forms so this wouldn't be an issue.

Lastly, you'd need to set ShowModal to False in order for the calling procedure to keep control.


Posted by Dave Hawley on May 28, 2001 8:43 AM

Dax, you are correct about setting ShowModal to False, I often get that one back to front.

You are wrong about the Screenupading not effecting UserForms though. try this:

Sub ShowForm
Application.ScreenUpdating = False
UserForm1.TextBox1 = "Hello"
End sub

Now try:

Sub ShowForm
Application.ScreenUpdating = True
UserForm1.TextBox1 = "Hello"
End sub

The other thing you will notice in the first example(other than the Text in Textbox1 not showing) is that if you move the UserForm with the Mouse it will cascade all over the screen. This must surely prove that it does surely effect UserForms.


OzGrid Business Applications

Posted by Dave Hawley on May 28, 2001 8:57 AM

Ignore that Textbox update bit, I need to go to bed!

OzGrid Business Applications

Posted by Dax on May 28, 2001 9:09 AM

You need to use the DoEvents keyword so that Windows has a chance to refresh the userform. Application.Screenupdating=False locks the window with the class name XLMAIN. Userform windows have class names THUNDER?FRAME and aren't locked. Stephen Bullen even includes a routine using API calls in one of his books that shows you how to freeze a userform because you are unable to using just VBA.


Posted by Dax on May 28, 2001 9:13 AM

Apologies Dave, didn't see this further post ; - )