How do I display a (non-modal) progress message?

[Jim Luedke]

Bit of a newbie Q: How do you best display a non-modal message to the
user?

You know, like MsgBox, only non-modal?

***

I have a UserForm. The user can click a button to start a process that
takes some minutes to run.

During that time, in the center of the screen I display a progress
message, "iiii out of jjjj thingies processed ..."

To do that, I use a Label.

The Label lives on the form. I keep the Label hidden. At display time
I reveal it, update it repeatedly with my message (not necessarily on
every iteration, depending on CPU speed), then hide it again when
done.

Do you think this is the best way to implement a progress message?

Thanks.

***

 

[Alan]

Jim,
If you mean a non-modal UserForm, then instead of this syntax:

UserForm.Show

just use this:

UserForm.Show False

However, be aware that the code after the show will then immediately
run.

I would recommend using a Progress Bar. See
http://digiassn.blogspot.com/2006/03/excel-progress-bar-indicator-with-vba.html.
You can make it show % complete, or display something like you do
already. Note that it requires you to write the code around the
progress update cycle.

Alan

 

[Patrick Molloy]

two more alternatives...
(2) use the status bar
Applicatiob.StatusBar = "some point"

(1)
Option Explicit
Dim sProgBar As Shape
Dim rProgBar As Range
Sub demo()
Dim t As Long
Dim i As Long
Dim max As Long
max = 100
For i = 1 To max
t = Timer
Do
DoEvents:
Loop Until Timer > t '(t + n) waits n seconds
ShowProgress i, max
Next
sProgBar.Delete
Set sProgBar = Nothing

End Sub


Sub ShowProgress(i As Long, max As Long)
If sProgBar Is Nothing Then
Set rProgBar = Range("C5:K5")
With rProgBar
Set sProgBar = ActiveSheet.Shapes.AddShape(msoShapeRectangle, .Top,
..Left, .Width, .Height)
sProgBar.Fill.ForeColor.SchemeColor = 12
End With
End If
sProgBar.Width = rProgBar.Width * i / max
End Sub

 

[Jim Luedke]

Alan, Patrick:

Thanks very much for reply.

My UserForm is ... er, what's a non-modal UserForm?

My form has all kindsa buttons & controls, and sits there as a
separate app. In fact I've enhanced it with Stephen Bullen's great
CFormChanger class. Which seems to be the next-best thing to what you
really want, namely a separate Windows app, written in your desired
language, that can read an Excel file. I know it can be done, but I'm
not willing to descend thru the mishpocha of data layers to do that.

If you are suggesting that I can flash a non-modal control on the
screen, and it just happens to be a UserForm, hey, that'd be great.

Plus I'll check out your two good ideas.

***

A ball of wax I didn't mention before is that my using a Label for a
progress message does have drawbacks:

- Since it's owned by the UserForm, I have a hard time getting it to
show if there's another control in front of it. In my case a TreeView
takes up much of the form. I must Hide the silly TV for the label to
show, then Show it again.

- I've tried enabling Label's great-sounding .AutoSize property,
because my message can vary in length, and it'd be great for the label
to size itself around the caption. But I must be misunderstanding
what .AutoSize does. Because if I enable it, at display time the whole
Label shinks to a microscopic size, with a font that looks like maybe
2 or 3 pixels. Yet, if you put a watch on the font size, its value at
that point is still the normal 8, 10, 12 etc. I mean, wha?

- I can't understand it, but no matter what I do, the Label seems to
stay hidden, even if I explicitly Enable and Show it before display. I
I must issue a UserForm.Repaint every time the caption changes. That
causes the label to appear just fine--except now there's a lot of
unsightly flashing on other parts of the form as well.

Some of these are likely newbie problems.

But I'll follow your suggestions.

Thanks again.

***

 

[Patrick Molloy]

by default, Userforms show as modal... ie
Userform1.Show
is by default, the same as
UserForm1.Show vbModal

modeless forms use the vmModeless parameter, ie
UserForm1.Show vbModeless
or
UserForm1.Show False

the thing is this. If your code shows a modal form , the code will stop
until the form is closed before continuing. If your form is shown modeless,
then the code continues to run and can be used to populate objects on the
form ..such as pushing values into a text box, or widening a label