VBA - Progress Bars

[michaeldh]

Dear Mr Excel,

I am building a spreadsheet which will use a progess bar on a userform to inform the user of the amount completed.

I would like to activate it from a button click within the spreadsheet(not on the userform) and whilst performing processing show the progress bar updating.

I have tried to do this but the operation performs first and then only a fully completed progress bar on the userform shows up after the operation is complete.

I need it to have the userform appearing firstly and updating at the same time as the operation is performing.

If you could advise me or provide some sample code, I would be very appreciative.

Attached is some sample code I am using as an example:

Private Sub UserForm_Initialize()

ProgressBar1.Min = 0
ProgressBar1.Max = 1000

For Count = 1 To 1000
ProgressBar1.Value = Count
'my code here
Sheets("Sheet1").Select
Range("A1").Select
ActiveCell = Count + 1
Next

End Sub
This message was edited by michaeldh on 2002-08-17 21:47

 

[stevebausch]

Don't get your hopes up that I can help, my help may not be adequate.

Which ProgressBar, the one from ComctlLib, or MSComctlLib? I am referring to Help for the ComctlLib progressbar, which references COMCTL32.OCX .

What I noticed when playing with ProgressBar, is that the aspect ratio is critical; the bar should be much longer than it is tall, otherwise the segments aren't very narrow.

And don't have the Max value too high, Max is what determines the division of the bar. If you have Max=100 on a small bar, the progression may be a bit jumpy. It won't move, then it moves.

Try a Max around 50, or even 25. I was incrementing my .Value by 1, and the bar wasn't stepping nicely unless the .Max was 25.

Oh, it looks like you are running a (test?) loop in the Initialize event routine. I think that's the problem; that code will run before the form becomes visible, for all intents and purposes.
This message was edited by stevebausch on 2002-08-17 22:54

 

[tusharm]

See also http://www.mrexcel.com/board/viewtopic.php?topic=19213&forum=2

 

[Legacy 98055]

Hi
You will need to call the Progressbar's refresh method each time it's value is updated. In your procedure above, place this line after your progressbar's value is incremented.
ProgressBar1.Refresh
Tom