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, Procedu="ShutDown",
Schedule:=False
End Sub

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.


--

HTH

RP

"Taffy" wrote in message
o.uk...
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, Procedu="ShutDown",
Schedule:=False
End Sub

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" wrote in message
...
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.


--

HTH

RP

"Taffy" wrote in message
o.uk...
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, Procedu="ShutDown",
Schedule:=False
End Sub

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

--

HTH

RP

"Taffy" wrote in message
o.uk...
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" wrote in message
...
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.


--

HTH

RP

"Taffy" wrote in message
o.uk...
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, Procedu="ShutDown",
Schedule:=False
End Sub

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

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, Procedu="ShutDown",
Schedule:=False
End Sub





"Bob Phillips" wrote in message
...
Just call the shutdown code for Case vbOK, where I use MsgBox "You clicked
OK"

--

HTH

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

"Taffy" wrote in message
o.uk...
Bob,
How do I call shutdown code, vba keeps shouting at me that I'm doing it
wrong:o)

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, Procedu="ShutDown",
Schedule:=False
End Sub





"Bob Phillips" wrote in message
...
Just call the shutdown code for Case vbOK, where I use MsgBox "You
clicked
OK"

--

HTH