Close msgbox automatically

E

EXCELMACROS

Hello, me again.., I'm trying to close a msgbox using application.ontime now
+ 5seg

Thank you in advance
 
M

Mike H

Hi,

How about this which auto closes after 5 seconds or sooner if the user
presses OK

CreateObject("WScript.Shell").popup "Your Message", 5

Mike
 
E

EXCELMACROS

I need to close it in 5 seg, can't wait for the user to press "ok" what do
you think?
 
E

EXCELMACROS

Well, I did copy paste exactly what you suggested and is not closing in 5
seg...
 
L

Leith Ross

Hello EXCELMACROS

Here is macro to close the dialog after 5 seconds. You can change thi
to suite your needs

Code
-------------------

Sub TimedMsgBox(

Dim Msg As Strin
Dim Secs As Lon
Dim Wsh As Objec

Title = "Test
Msg = "This will close in 5 seconds.
Secs =

Set Wsh = CreateObject("WScript.Shell"
RetVal = Wsh.Popup(Msg, Secs, Title, vbInformation + vbOKOnly
Set Wsh = Nothin

End Su
 
M

Mike H

Hi,

Then I'm at a loss to understand why. I don't believe you need to set any
references to make this work but perhaps someone else can enlighten us (me).
Once again for me it displays for 5 seconds and then auto closes.

I'll post it again in case tit became corrupted last time

CreateObject("WScript.Shell").Popup "Your Message", 5, "User Message"

Mike

Mike
 
E

EXCELMACROS

Sorry Mike, It won't close until I press "OK" Thank you so much for your
help...
 
P

Peter T

The WScript.Shell - popup method is not reliable to dismiss a msgbox. It may
work in some machines, not at all, or erratically (does not seem to relate
to windows version).

Best way is make a userform to look like a msgbox, perhaps pass a message to
a label caption and include some sort of timer to dismiss the form if still
showing after say 5 seconds, eg

' userform code
Private mbShow As Boolean

Private Sub UserForm_Activate()
Dim t As Single
Dim ShowTime As Single

mbShow = True

ShowTime = 5
t = Timer

While (Timer < ShowTime + t) And mbShow
DoEvents
Wend

If mbShow Then
Unload Me
End If
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
mbShow = False
End Sub

Regards,
Peter T
 
O

Orion Cochrane

Set a reference to Windows Script Host Object Model (Tools > References).
Copy and paste the TimedMsgBox code and try it. It works for me.
 
M

Mike H

Hi,

In XL2003, I can run the solution posted by me and the one by Leith without
setting this reference.

Mike
 
E

EXCELMACROS

Thank you all, I'm also on XL2003, very weird I'll try on a different
computer tonight and let you know...
 
P

Peter T

Not weird, no need to be surprised if it doesn't work for you as I tried to
explain in my other post.

The reference is not required if using late binding and createobject, but
that's not the issue.

Regards,
Peter T
 
O

Orion Cochrane

No problem. I was hoping to avoid the userform route, but if that's the only
way, then it must be done.
 
J

JLGWhiz

Works for me too, Mike. Must be something in the OP settings that prevents
it from executing.
 
G

Gord Dibben

Doesn't work for me in my 2003 setup.

The msgbox appears on the screen and also as a new entry(Test) on my
Taskbar??

But never closes on its own.


Gord Dibben MS Excel MVP
 
Top