Time Out a MsgBox

[JT1977]

I have a file that runs a macro when it is open. This file may be opened
manually or automatically by a macro from another file.

If the file is opened manually I would like a MsgBox to prompt the user to
end the macro before it gets into the body of the code.

If the file is opened automatically I would like the MsgBox to automatically
respond after a time delay to allow the macro to continue running the bulk of
the code.

Is there anyway to force a response to a MsgBox after a preset amount of
time with no response from the user?

 

[Mike H]

Hi,

How about this instead which closes after 5 seconds or when OK is pressed

CreateObject("WScript.Shell").Popup "Some message", 5, "Your Title"
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Peter T]

Mike, unfortunately that method is very unreliable, it works for some but
not others.

One alternative is to make a similar looking userform, set to automatically
unload with a timer (from a previous post)

' 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

Obviously include relavant buttons and appropriate code code to similate the
msgbox.

Regards,
Peter T

 

[Mike H]

Peter,

Thanks for that. I read it's unreliable elsewhere but have never been able
to replicate a fault despite trying different machines & 3 different versions
of Excel. Do you know or can you point me in the direction of when it may
fail?
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Peter T]

Pretty sure it first came up in this thread, starting from where I came in -

http://groups.google.co.uk/group/mi...man++group:microsoft.public.excel.programming

The long and short of it was it might work for some but not others. No
obvious explanation, but not related to OS, XL version, WScript enabled,
etc. That was 5 years ago but FWIW it doesn't work in my current machine
either.

Regards,
Peter T

 

[ker_01]

Please also see responses to the same thread you posted yesterday. If those
(or these) thread responses aren't what you are looking for, you may have
better luck posting a followup response indicating what you tried and what
still isn't working, rather than reposting.

Best,
Keith

 

[Mike H]

Well if people are reporting it's unreliable then I gues it's the userform
option from now on. Thanks for the link
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[JT1977]

Thanks for the reply. Sorry about the double post. I couldn't find
yesterday's post so I thought it didn't go through and reposted.