progressbar on a userform

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
DoEvents
End Sub

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

I hope this is clear,
Regards,
Dax.

Hi Amroo

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

or

Sheet1.Visible = xlSheetVeryHidden

or

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.

Dave

OzGrid Business Applications

Amroo, here is a link to: a "Progress Indicator" download example: http://j-walk.com/ss/excel/files/developer.htm

DaveOzGrid Business Applications

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.

Regards,
Dax.

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.Show
UserForm1.TextBox1 = "Hello"
End sub

Now try:

Sub ShowForm
Application.ScreenUpdating = True
UserForm1.Show
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.

Dave

OzGrid Business Applications

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

Dave
OzGrid Business Applications

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.

Regards,
Dax.

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