MsgBox Closes Automatically

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

--
achidsey

You could use something like:

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



achidsey wrote:
>
> 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
>
> --
> achidsey

--

Dave Peterson

Dave,

Thanks for the help.

Alan

--
achidsey


"Dave Peterson" wrote:

> You could use something like:
>
> CreateObject("WScript.Shell").Popup "Hello", 4, _
> "This closes itself in 4 seconds"
>
>
>
> achidsey wrote:
> >
> > 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
> >
> > --
> > achidsey
>
> --
>
> Dave Peterson
>

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

"achidsey" <[email protected](notmorespam)> wrote in message
news:[email protected]...
>
> 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
>
> --
> achidsey

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

RBS

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> You could use something like:
>
> CreateObject("WScript.Shell").Popup "Hello", 4, _
> "This closes itself in 4 seconds"
>
>
>
> achidsey wrote:
>>
>> 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
>>
>> --
>> achidsey
>
> --
>
> Dave Peterson

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


"RB Smissaert" <[email protected]> wrote in message
news:[email protected]...
>I tried it, but it doesn't close.
> Can I just put this straight in a Sub without anything else?
>
> RBS
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
>> You could use something like:
>>
>> CreateObject("WScript.Shell").Popup "Hello", 4, _
>> "This closes itself in 4 seconds"
>>
>>
>>
>> achidsey wrote:
>>>
>>> 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
>>>
>>> --
>>> achidsey
>>
>> --
>>
>> 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.

RB Smissaert wrote:
>
> I tried it, but it doesn't close.
> Can I just put this straight in a Sub without anything else?
>
> RBS
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> > You could use something like:
> >
> > CreateObject("WScript.Shell").Popup "Hello", 4, _
> > "This closes itself in 4 seconds"
> >
> >
> >
> > achidsey wrote:
> >>
> >> 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
> >>
> >> --
> >> achidsey
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

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

RBS

"achidsey" <[email protected](notmorespam)> wrote in message
news:[email protected]...
>
> 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
>
> --
> achidsey

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

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> 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 wrote:
>>
>> I tried it, but it doesn't close.
>> Can I just put this straight in a Sub without anything else?
>>
>> RBS
>>
>> "Dave Peterson" <[email protected]> wrote in message
>> news:[email protected]...
>> > You could use something like:
>> >
>> > CreateObject("WScript.Shell").Popup "Hello", 4, _
>> > "This closes itself in 4 seconds"
>> >
>> >
>> >
>> > achidsey wrote:
>> >>
>> >> 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
>> >>
>> >> --
>> >> achidsey
>> >
>> > --
>> >
>> > Dave Peterson
>
> --
>
> Dave Peterson

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

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> 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 wrote:
> >
> > I tried it, but it doesn't close.
> > Can I just put this straight in a Sub without anything else?
> >
> > RBS
> >
> > "Dave Peterson" <[email protected]> wrote in message
> > news:[email protected]...
> > > You could use something like:
> > >
> > > CreateObject("WScript.Shell").Popup "Hello", 4, _
> > > "This closes itself in 4 seconds"
> > >
> > >
> > >
> > > achidsey wrote:
> > >>
> > >> 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
> > >>
> > >> --
> > >> achidsey
> > >
> > > --
> > >
> > > Dave Peterson
>
> --
>
> Dave Peterson

Hi Peter,

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

RBS

"Peter T" <peter_t@discussions> wrote in message
news:[email protected]...
> 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
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
>> 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 wrote:
>> >
>> > I tried it, but it doesn't close.
>> > Can I just put this straight in a Sub without anything else?
>> >
>> > RBS
>> >
>> > "Dave Peterson" <[email protected]> wrote in message
>> > news:[email protected]...
>> > > You could use something like:
>> > >
>> > > CreateObject("WScript.Shell").Popup "Hello", 4, _
>> > > "This closes itself in 4 seconds"
>> > >
>> > >
>> > >
>> > > achidsey wrote:
>> > >>
>> > >> 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
>> > >>
>> > >> --
>> > >> achidsey
>> > >
>> > > --
>> > >
>> > > Dave Peterson
>>
>> --
>>
>> Dave Peterson
>
>

> Yes there was a long thread last summer

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

Regards,
Peter T

"Peter T" <peter_t@discussions> wrote in message
news:[email protected]...
> 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
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
<snip>