userform status

[andrew299]

Hello again
I want to create a sort of status bar for my macro. I have the code that calculates the percentage complete but how do I display this?
I thought first of all about msgboxes but dont want to click OK everytime (could be 400 times +).
So I thought ahh! userforms - created one that displays without OK by using labels but still have to click the x.
Then I looked in the FAQs and found that I could turn off the x - so I did. But now there is no way of removing the userform and therefore the progrma wont run further.
Any ideas as to how i can just have a display box that updates with every cycle of the loop?

Thanks
Andrew299

 

[RobBroekhuis]

Using a userform with a single label is definitely the easiest way to go. How to do it depends on which version of Excel (I'm assuming?) you are running - if it's 2000 or better, you can use a modeless userform, which stays up while your code keeps on running:

frmProgress.show vbModeless
do
...do your stuff here
...calculate %completion
frmProgress.lblProgress= MyProgressPct & "% completed"
loop until done
unload frmProgress

You can make it a little fancier by using the progressbar control, which shows the typical progress bar we all know and love. You can select it as an "additional control" by right-clicking on the controls toolbox in VBE - I think it's name is Microsoft Progress Bar Control, or something similar.
Rob

 

[andrew299]

Thanks Rob

I found this

http://j-walk.com/ss/excel/tips/tip34.htm

which I think does what you have described above

Andrew