userForm as popup message while calculating

**gtmeloney** · 06-03-2009, 11:09 AM

I am building a macro that deals with a lot of data and takes several minutes to run. I want to display a message to the user stating "Calculating..." while the calculating is taking place. I got the idea to use a userForm with a label on it. The problem is that excel waits for input from the userform before continuing the calculations.

How do I display the userform, continue doing the calculations, and then hide the userform when the calculations are complete? (without any input from the user)

I currently have:

Please Login or Register  to view this content.

**royUK** · 06-03-2009, 11:14 AM

The UserForm needs to be displayed modless. In the VB Editor set it's ShowModal property to False.

**gtmeloney** · 06-03-2009, 11:47 AM

Thanks Roy.

My problem now is that the label doesn't get displayed, rather the userform is white since so much processing is going on in the background (I think). Is there any way around this?

**gtmeloney** · 06-03-2009, 12:32 PM

In case anyone ever reads this: I found a technique that is better suited to what I need. I use the status bar to display a box on each iteration of my loop, indicating to the user that calculations are still being done (and that the system is not hung).

I found this technique here: http://www.dailydoseofexcel.com/arch.../progress-bar/

I also found that by setting the status bar, I eliminate Excel's updating of the status bar (which was annoying because I'm doing so many calculations)

**nur87** · 11-02-2011, 11:04 AM

Originally Posted by royUK

The UserForm needs to be displayed modless. In the VB Editor set it's ShowModal property to False.

this works with a defect. i wanted to display a pic on the userform, but it didnt show anything, what it gave me was an empty white userform with its caption... do you know how this problem can be solved?

thanks

**arthurbr** · 11-02-2011, 11:28 AM

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.