MsgBox Closes Automatically

[Guest]

Excel Experts,

Is there any way to close a message box without the user having to click "OK".

I want to show a message t other user for a few seconds and then have the
message box disappear without the user having to click the OK button.

My code is similar to the following:

Sub EnterNewShorts()

Set NewShorts = Cells.Find(What:="NewShort")

If NewShorts Is Nothing Then

MsgBox "No New Shorts"
Exit Sub

Else
(rest of code)

End Sub

I know how to pause the code for a few seconds. Then I'd like to add code
that closes the message box. Is this possible?

Thanks,
Alan

 

[Dave Peterson]

You could use something like:

CreateObject("WScript.Shell").Popup "Hello", 4, _
"This closes itself in 4 seconds"

 

[Guest]

Dave,

Thanks for the help.

Alan

--
achidsey

You could use something like:

CreateObject("WScript.Shell").Popup "Hello", 4, _
"This closes itself in 4 seconds"

 

[Joe]

Hi,

Add a userform.

Put a label in the Userform that has the message you want ("No New Shorts")

Then add this code to the userform activate event

Private Sub UserForm_Activate()

MyNumber = Timer

Do Until MyNumber + 5 < Timer

DoEvents

Loop

UserForm1.Hide

End Sub

That will display the message for about 5 seconds.

=============

In your main code add this

UserForm1.Show

To display the userform.

Best of Luck,

Joe

 

[RB Smissaert]

I tried it, but it doesn't close.
Can I just put this straight in a Sub without anything else?

RBS

 

[RB Smissaert]

When I save the workbook, close Excel, and try it again it works.
Thanks for the tip; will see if I have some use for it.

RBS

I tried it, but it doesn't close.
Can I just put this straight in a Sub without anything else?

RBS

 

[Dave Peterson]

I've seen posts that say that the message box doesn't close for them.

IIRC, it was a different version of windows that caused the trouble. If it
doesn't work for you, then I don't think that there's any tweak you can make to
it.

I tried it, but it doesn't close.
Can I just put this straight in a Sub without anything else?

RBS

 

[RB Smissaert]

Here is a way to do this with the Windows API:
http://www.xcelfiles.com/API_02.html

RBS

 

[RB Smissaert]

I read other posts saying that the timing wasn't always reliable, so I think
I won't use this.
Maybe the best way is with the API. A userform will be simpler, but it seems
a lot of resources
for a simple message box.

RBS

I've seen posts that say that the message box doesn't close for them.

IIRC, it was a different version of windows that caused the trouble. If
it
doesn't work for you, then I don't think that there's any tweak you can
make to
it.

 

[Peter T]

Yes there was a long thread last summer (do doubt others too). Although it
worked for some, not at all for others (incl me), and spasmodically for
others (irregular times). There wasn't any obvious commonality in systems in
which it worked or otherwise. The consensus was not reliable for general
distribution.

Regards,
Peter T

I've seen posts that say that the message box doesn't close for them.

IIRC, it was a different version of windows that caused the trouble. If it
doesn't work for you, then I don't think that there's any tweak you can make to
it.

 

[RB Smissaert]

Hi Peter,

Have come to the same conclusion, but I can't really see any use for it in
any case.

RBS

Yes there was a long thread last summer (do doubt others too). Although it
worked for some, not at all for others (incl me), and spasmodically for
others (irregular times). There wasn't any obvious commonality in systems
in
which it worked or otherwise. The consensus was not reliable for general
distribution.

Regards,
Peter T

 

[Peter T]

Yes there was a long thread last summer

To prevent complaints of geographical ambiguity, I wrote that in the
northern hemisphere!

Regards,
Peter T

Yes there was a long thread last summer (do doubt others too). Although it
worked for some, not at all for others (incl me), and spasmodically for
others (irregular times). There wasn't any obvious commonality in systems in
which it worked or otherwise. The consensus was not reliable for general
distribution.

Regards,
Peter T

<snip>