View Full Version : Progress
ProteanBeing
03-14-2008, 11:47 AM
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
03-14-2008, 12:37 PM
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
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
03-14-2008, 09:35 PM
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.
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
03-16-2008, 05:05 AM
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".
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.