Self closing "modal" window ?

[Michel S.]

Hello !

I'm modifying an application written in Excel 2003 VBA.

After an operation ends, I'd like to display a confirmation message in
a window that will close itself after a given period of time (2
seconds).

This way, the user will get the feedback whithout having to click on a
MsgBox like "Ok" button.

I succesfully programmed a modeless form which will correctly do this
when used alone.

The problem is that in the application, this form is invoked from
another form which is modal. Upon displaying the confirmation message,
I get an error message saying that a non-modal window can't be open
while a modal window is open.

While making the calling modal form "modeless" may solve the problem in
that specific case, this worries me a little because this way, the user
can access the spreadsheet behind while the form is open and this is
not desired.

Searching on the net, I also found a code sample that uses the windows
api to simulate a "timer" object on a form, but was unsuccessful to
make it work.

Do anybody knows if this can be done, and if so, how ?

Thanks !

 

[NickHK]

Michel,
Whilst you cannot show a modeless form when a modal is shown, you can show
another modal form.

NickHK

 

[Michel S.]

Thanks NickHK for your reply,

Here's a code snippet I use to Show the form and then close it two
seconds later :

Me.Show vbModeless
varTarget = DateAdd("s", 2, Now)
Do
DoEvents
Loop Until Now > varTarget
Unload Me

The problem I face if I make the window modal is that when the code
reach the Me.Show (without vbModeless) statement, it sits there,
waiting for the user to do something. Since the code following is not
executed, the windows never closes itself.

As I said earlier, I found a code sample to simulate a Timer object
using the windows api to replace the above loop, but was unable to make
it work either.

Any idea ?


NickHK a couché sur son écran :

 

[NickHK]

Michel,
OK, I got you. Maybe one of these:
http://www.google.co.uk/search?hl=en&q=timed+message+box+Excel&btnG=Google+Search&meta=

NickHK

 

[Chip Pearson]

You can use an OnTime procedure to dismiss the form after a certain period
of time. In a standard code module, use code like the following:

Public RunWhen As Double

Sub ShowTheForm()
RunWhen = Now + TimeSerial(0, 0, 10) ' close 10 seconds from now
Application.OnTime RunWhen, "CloseTheForm"
UserForm1.Show vbModal
End Sub

Sub CloseTheForm()
Unload UserForm1
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)