Progress

[ProteanBeing]

Is it possilbe to create a dialog box (or does excel have one) that can show the percentage of the macro complete (my macros takes a long time, I want the user to know it's still working).

[ProteanBeing]

Found progress bar object. However I also want to show the percentage. I have the value going to a textbox but the value only shows when the whole thing is complete (100%) This is the code:
For Each MaterialNumber In ThisWorkbook.Worksheets("Log").Range("A3:A60000")

Percentage = Int((MaterialNumber.Row / 60000) * 100)
Me.LabelPercentage.Text = Format(Percentage) + " %"
ProgressBar1.Value = Percentage
If MaterialNumber.Text = Me.TextMaterialNumber.Value Then
ReDim Preserve ComboData(3, counter)
ComboData(0, counter) = MaterialNumber.Text
ComboData(1, counter) = MaterialNumber.Range("B1").Text
ComboData(2, counter) = MaterialNumber.Row
counter = counter + 1
End If
Next MaterialNumber

[Ago]

http://vbaexpress.com/kb/getarticle.php?kb_id=87

http://vbaexpress.com/kb/getarticle.php?kb_id=993

useing userforms normally makes excel lock up.

[gwkenny]

useing userforms normally makes excel lock up.

???

How so? Freeze the computer?

Or do you mean halts execution till the Userform is 'processed' or acted upon?

I use the progressbar object a lot and it has never locked up on me. You do have to show the form in modeless mode though. It's easy to use.

[Ago]

in taskmanager excel has the status not responding for me.
but excel is still doing the calculations in the bakground but i cant see the updates on the sheets.
same thing with the userform, it stops at a percentage and doesnt move untill the calculations is done.

it could be because i use 2003. 2003 doesnt have the dualcore support so that might be whats creating the problem

[gwkenny]

Excel is not responding to the OS cause it's busy processing (though that explanation just sounds weird in and of itself). I don't think dualcore would help that at all unfortunately :(

You can't see the update in the sheets because either you've turned screenupdating off, or Excel is so busy processing that it can't update the screen. You can force a screen refresh in your code or use doevents.

"same thing with the userform, it stops at a percentage and doesnt move untill the calculations is done."

If you are using a modeless form, the userform won't 'move' until you update the % of the progressbar.

I'm still confused on how Excel is locked up, or maybe we've just aren't communicating properly because we have different definitions for words like "locked up" and "calculations".