Displaying a Progress Message

[klyhthwy]

Hello,

I am looking for a way to display a message box in VBA that will not provide any options (i.e. Ok, Cancel), but will just display a message to "Please Wait" while another process is running. I would like the message box to be cleared when the process is complete.

Any good way to do this?

Thanks,

Kelly

 

[chuckchuckit]

It cannot be done using msgbox. There are a couple ways I know of that use Application.OnTime or UserForms that use the vbModless parameter. I have used both. If there are easier ways, then I am not aware of them.

Breifly discussed those two ways yesterday here:

http://www.mrexcel.com/forum/showthread.php?t=573661

 

[shg]

You can display it using Application.StatusBar

 

[chuckchuckit]

shg,

I think that is a section of the screen Excel allocates for such, rather than in a box, if I remember correctly?

 

[shg]

It's the text at the lower left of the screen that shows Excel's state (Ready, Calculating, Edit, ...)

 

[chuckchuckit]

I looked at "Application.StatusBar " some months ago before using the other two approaches. The "Application.StatusBar " likely is good enough for many, but I could not get it to work for what I needed. As more flexability was required so went those other routes. The code got a bit intensive depending upon need, but very adaptable.

I searched for other ways that could use a box that pops up, but was stuck with making my own custom status bars.

If there is a better way would be interested in looking at them for sure.

 

[shg]

Andy Pope has a nice collection of progress meters at http://www.andypope.info/vba/pmeter.htm

 

[chuckchuckit]

Hey nice! Those look like they might work well. Worth a look.

Thanks shg

 

[phagan]

Kelly,

I have a number of spreadsheets that I do that for. The best way I found is to creat a userform that just has the message you want them to see then right before the process starts you show the userform and right after the process you unload the userform.

The best thing about that is that you can make it look exactly how you want. I use our company logo on them.

 

[chuckchuckit]

Breifly looked at the Andy Pope collection of progress meters referred by shg.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
They all use the UserForm way we have been discussing here. So this might point to the "UserForm" way as the best way to make a "Progress Meter" that would be highly customisable. So there may not be a better way in Excel to do such. I was not able to access his VBA code, which would have made it nice to see how he did all those various Progress Meters.
<o></o>
Except I like the "Application.OnTime" way that has no interferrence with other computer and Excel functions. Perhaps using "Application.OnTime" and "Userforms with it's vbModeless parameter" together would be a real efficient way. One would have to look into Andy Pope's Progress Meters closely to be sure they do interfere with user interface things like copy and paste, which my first indications say they do.<o></o>
<o></o>
I like the flexibility of writing the code myself so I can better understand what is going on in case I need to modify things for different applications later.<o></o>
<o></o>
Our discussions here seem to say that the "UserForms" way with the "vbModlesss" parameter is likely indeed the best way to make your own Progress Meter". Learning how to use the "UserForms" way is made a lot easier through this video:

http://www.youtube.com/watch?v=5PN7lWJSobQ
<o></o>