auto close while MsgBox is open

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Jim Rech cited a self closing msgbox if you have the windows scripting host:

From: "Jim Rech" <[email protected]>
References: <024001c23a01$584dfec0$37ef2ecf@TKMSFTNGXA13>
<e2XDD2hOCHA.1728@tkmsftngp12> <OUq7WIhPCHA.496@tkmsftngp09>
<##np6ohPCHA.2624@tkmsftngp13> <OHr91qiPCHA.1996@tkmsftngp12>
Subject: Re: 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)
 
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" <[email protected]>
References: <024001c23a01$584dfec0$37ef2ecf@TKMSFTNGXA13><e2XDD2hOCHA.1728@tkmsftngp12><OUq7WIhPCHA.496@tkmsftngp09><##np6ohPCHA.2624@tkmsftngp13><OHr91qiPCHA.1996@tkmsftngp12>
Subject: Re: 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)
 
Back
Top