Close a MsgBox

[Taffy]

I have the following code, if the user does not respond how do I get the
msgbox to close. After the set time is up if you press the OK or the Cancel
button the workbook is closed but because the msgbox has the focus it only
happens when the msgbox has gone.

Thanks Taffy

Dim DownTime As Date

Sub SetTime()
DownTime = Now + TimeValue("00:00:10") 'change time as needed
Application.OnTime DownTime, "SelfClosingMsgBox"
End Sub

Sub SelfClosingMsgBox()
Dim msgvar As Integer
Application.OnTime Now + TimeSerial(0, 0, 10), "ShutDown"
msgvar = MsgBox("Timer Test Closing in 10 Seconds", vbOKCancel, "Excel")
If msgvar = vbOK Then ShutDown
If msgvar = vbCancel Then Disable
End Sub

Sub ShutDown()
ThisWorkbook.Save
ThisWorkbook.Close
End Sub

Sub Disable()
On Error Resume Next
Application.OnTime EarliestTime:=DownTime, Procedure:="ShutDown",
Schedule:=False
End Sub

 

[Bob Phillips]

Taffy,

Here is an example of a timed msgbox.

Dim cTime As Long
Dim WSH As Object

Set WSH = CreateObject("WScript.Shell")
cTime = 10 ' 10 secs
Select Case WSH.Popup("Open an Excel file?!", cTime, "Question", _
vbOKCancel)
Case vbOK
MsgBox "You clicked OK"
Case vbCancel
MsgBox "You clicked Cancel"
Case -1
MsgBox "Timed out"
Case Else
End Select


As you can see, it can be OK, Cancel or timed out.

 

[Taffy]

Bob,

Thanks for the code, how do I get it to call the shutdown code when the OK
button is pressed or when the message is timed out rather than pop up
another msgbox?

Thanks Taffy

 

[Bob Phillips]

Just call the shutdown code for Case vbOK, where I use MsgBox "You clicked
OK"

 

[Taffy]

Bob,
How do I call shutdown code, vba keeps shouting at me that I'm doing it
wrong)

Taffy Thanks


Dim DownTime As Date

Sub SetTime()
DownTime = Now + TimeValue("00:00:10") 'change time as needed
Application.OnTime DownTime, "timedmsg"
End Sub

Sub timedmsg()
Dim cTime As Long
Dim WSH As Object

Set WSH = CreateObject("WScript.Shell")
cTime = 10 ' 10 secs
Select Case WSH.Popup("Excel closing", cTime, "Question", _
vbOKCancel)
Case vbOK 'Call Shudown
Case vbCancel 'Call Disable
Case -1 'call Shutdown

Case Else
End Select

End Sub

Sub ShutDown()
ThisWorkbook.Save
ThisWorkbook.Close
End Sub

Sub Disable()
On Error Resume Next
Application.OnTime EarliestTime:=DownTime, Procedure:="ShutDown",
Schedule:=False
End Sub

 

[Taffy]

Bob,

Got it, had call on the same line as case

Case vbOK
Call ShutDown
Case vbCancel
Call SetTime
Case -1
Call ShutDown

Now it works

Thanks Taffy

Bob,
How do I call shutdown code, vba keeps shouting at me that I'm doing it
wrong)

Taffy Thanks


Dim DownTime As Date

Sub SetTime()
DownTime = Now + TimeValue("00:00:10") 'change time as needed
Application.OnTime DownTime, "timedmsg"
End Sub

Sub timedmsg()
Dim cTime As Long
Dim WSH As Object

Set WSH = CreateObject("WScript.Shell")
cTime = 10 ' 10 secs
Select Case WSH.Popup("Excel closing", cTime, "Question", _
vbOKCancel)
Case vbOK 'Call Shudown
Case vbCancel 'Call Disable
Case -1 'call Shutdown

Case Else
End Select

End Sub

Sub ShutDown()
ThisWorkbook.Save
ThisWorkbook.Close
End Sub

Sub Disable()
On Error Resume Next
Application.OnTime EarliestTime:=DownTime, Procedure:="ShutDown",
Schedule:=False
End Sub

 

[Bob Phillips]

Taffy,

you can have it on the same line, just put a stement separator in, a colon

Case vbOK : Call ShutDown

--

HTH

RP

Bob,

Got it, had call on the same line as case

Case vbOK
Call ShutDown
Case vbCancel
Call SetTime
Case -1
Call ShutDown

Now it works

Thanks Taffy