auto close while MsgBox is open
 

My code includes the following.

Dim Message, Title, Response
Message = "Entry must be numeric."
Title = "Invalid Entry"
Response = MsgBox(Message, Style, Title, Help, Ctxt)

and, in a separate module,

Sub Auto_Open()
' bunch of code
Application.OnTime Now + TimeValue("00:00:30"), "Close_Workbook"
' more code
End Sub
Sub Close_Workbook()
Application.DisplayAlerts = False
ThisWorkbook.Close
End Sub

I want to guard against someone walking away without responding with an "OK" to the message.
Is there a way I can get the workbook to automatically close while the MsgBox is still open?

Thanks in advance.


Kevin

auto close while MsgBox is open
 

Jim Rech cited a self closing msgbox if you have the windows scripting host:

From: "Jim Rech"
References: <024001c23a01$584dfec0$37ef2ecf@TKMSFTNGXA13
<e2XDD2hOCHA.1728@tkmsftngp12 <OUq7WIhPCHA.496@tkmsftngp09
<##np6ohPCHA.2624@tkmsftngp13 <OHr91qiPCHA.1996@tkmsftngp12
Subject: MsgBox without asking user to click <OK
Date: Wed, 7 Aug 2002 13:50:39 -0400
Lines: 15
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1079
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1079
Message-ID: <#ghpFrjPCHA.2048@tkmsftngp08
Newsgroups: microsoft.public.excel.programming
NNTP-Posting-Host: 199.207.253.101


No, you do not need a reference when you use CreateObject. You are running
this from Excel rather than the VBE? The only time I had a problem was when
I started it from the VBE and switched to Excel while it was running.

Sub SelfClosingMsgBox()
Dim wsh As Object
Set wsh = CreateObject("WScript.Shell")
wsh.Popup "Hello!", 2, "This closes itself in 2 seconds"
End Sub

--
Jim Rech
Excel MVP
----------------------------
Some people responsed that it didn't close for them. I didn't have any
problem with it.

Tested in xl97 SR2, windows 98 SE
xl2000, Windows 2000
xl2000, Windows XP Pro (although it seemed to hang around
longer than 2 seconds)
--
Regards,
Tom Ogilvy

Kevin wrote in message
...
My code includes the following.

Dim Message, Title, Response
Message = "Entry must be numeric."
Title = "Invalid Entry"
Response = MsgBox(Message, Style, Title, Help, Ctxt)

and, in a separate module,

Sub Auto_Open()
' bunch of code
Application.OnTime Now + TimeValue("00:00:30"), "Close_Workbook"
' more code
End Sub
Sub Close_Workbook()
Application.DisplayAlerts = False
ThisWorkbook.Close
End Sub

I want to guard against someone walking away without responding with an
"OK" to the message.
Is there a way I can get the workbook to automatically close while the
MsgBox is still open?

Thanks in advance.


Kevin

auto close while MsgBox is open
 

You've come through again Tom. It works just fine.

Thanks much.


Kevin

----- Tom Ogilvy wrote: -----

Jim Rech cited a self closing msgbox if you have the windows scripting host:

From: "Jim Rech"
References: <024001c23a01$584dfec0$37ef2ecf@TKMSFTNGXA13<e2XD @tkmsftng 6@tkmsftngp12
Subject: MsgBox without asking user to click <OK
Date: Wed, 7 Aug 2002 13:50:39 -0400
Lines: 15
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1079
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1079
Message-ID: <#ghpFrjPCHA.2048@tkmsftngp08
Newsgroups: microsoft.public.excel.programming
NNTP-Posting-Host: 199.207.253.101


No, you do not need a reference when you use CreateObject. You are running
this from Excel rather than the VBE? The only time I had a problem was when
I started it from the VBE and switched to Excel while it was running.

Sub SelfClosingMsgBox()
Dim wsh As Object
Set wsh = CreateObject("WScript.Shell")
wsh.Popup "Hello!", 2, "This closes itself in 2 seconds"
End Sub

--
Jim Rech
Excel MVP
----------------------------
Some people responsed that it didn't close for them. I didn't have any
problem with it.

Tested in xl97 SR2, windows 98 SE
xl2000, Windows 2000
xl2000, Windows XP Pro (although it seemed to hang around
longer than 2 seconds)
--
Regards,
Tom Ogilvy

Kevin wrote in message
...
My code includes the following.
Dim Message, Title, Response
Message = "Entry must be numeric."
Title = "Invalid Entry"
Response = MsgBox(Message, Style, Title, Help, Ctxt)
and, in a separate module,
Sub Auto_Open()
' bunch of code
Application.OnTime Now + TimeValue("00:00:30"), "Close_Workbook"
' more code
End Sub
Sub Close_Workbook()
Application.DisplayAlerts = False
ThisWorkbook.Close
End Sub
I want to guard against someone walking away without responding with an
"OK" to the message.
Is there a way I can get the workbook to automatically close while the
MsgBox is still open?
Thanks in advance.
Kevin