Close msgbox automatically

[EXCELMACROS]

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

Thank you in advance

 

[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

 

[EXCELMACROS]

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

 

[Mike H]

I think you need to go back and read my post again.

Mike

 

[EXCELMACROS]

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

 

[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

 

[EXCELMACROS]

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

 

[EXCELMACROS]

Sorry Leith, it did not work. I have to click "OK" for it to close...

 

[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

 

[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.

 

[Mike H]

Hi,

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

Mike

 

[Orion Cochrane]

I took the reference out, and it worked as well for me too.

 

[EXCELMACROS]

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

 

[EXCELMACROS]

This method worked, thank you MIKE ; LEITH ; ORION and PETER

 

[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

 

[Orion Cochrane]

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

 

[JLGWhiz]

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

 

[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