Sub ShutDown()
MsgBox "If there is no Activity in 5 seconds, Excel will save your work
and close. Do you need more time?", vbYesNo
On Error Resume Next
If vbYes Then
Call SetTime
Exit Sub
Else
DownTime = Now + TimeValue("00:00:05")
Application.OnTime DownTime, "ShutDown"
Call GonnaSave
End If
On Error GoTo 0
End Sub

I'm testing out some auto-close code. I'm wondering if the MsgBox actually
needs a user to click 'No' for the 'Else' part of the macro to fire. I think
so... How can I tell excel to go ahead and run the 'Else' if 'Yes' is NOT
clicked in 10-seconds?

As always, thanks!

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

Hi,

You can do this

BtnCode = CreateObject("WScript.Shell").Popup("Your Text", 5, "Title", 4 + 32)
MsgBox BtnCode

This produces a popup message box that times out after 5 seconds. You can
then test which button was pressed to decide what to do next

-1 No button pressed
6= yes
7 =No

Have a look here for more info on this method and what the numbers at the
end do

http://msdn.microsoft.com/en-us/libr...9f(VS.85).aspx
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"ryguy7272" wrote:

Sub ShutDown()
MsgBox "If there is no Activity in 5 seconds, Excel will save your work
and close. Do you need more time?", vbYesNo
On Error Resume Next
If vbYes Then
Call SetTime
Exit Sub
Else
DownTime = Now + TimeValue("00:00:05")
Application.OnTime DownTime, "ShutDown"
Call GonnaSave
End If
On Error GoTo 0
End Sub

I'm testing out some auto-close code. I'm wondering if the MsgBox actually
needs a user to click 'No' for the 'Else' part of the macro to fire. I think
so... How can I tell excel to go ahead and run the 'Else' if 'Yes' is NOT
clicked in 10-seconds?

As always, thanks!

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

In VBA, go to the Tools menu, choose References, and then Windows
Script Host Object Model. Then, use code like


Dim R As VbMsgBoxResult
With New IWshRuntimeLibrary.WshShell
R = .Popup("Click Yes Or No", 5, , vbYesNo + vbDefaultButton2)
End With

If R = vbYes Then
' user clicked yes
Else
' user clicked no
End If

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com






On Tue, 11 May 2010 11:56:01 -0700, ryguy7272
wrote:

Sub ShutDown()
MsgBox "If there is no Activity in 5 seconds, Excel will save your work
and close. Do you need more time?", vbYesNo
On Error Resume Next
If vbYes Then
Call SetTime
Exit Sub
Else
DownTime = Now + TimeValue("00:00:05")
Application.OnTime DownTime, "ShutDown"
Call GonnaSave
End If
On Error GoTo 0
End Sub

I'm testing out some auto-close code. I'm wondering if the MsgBox actually
needs a user to click 'No' for the 'Else' part of the macro to fire. I think
so... How can I tell excel to go ahead and run the 'Else' if 'Yes' is NOT
clicked in 10-seconds?

As always, thanks!

WOW! Fantastic! Thanks Chip. Here's the final version:

(This code goes behind the sheet)
Public CloseDownTime As Variant

Public Sub ResetTimer()
On Error Resume Next
If Not IsEmpty(CloseDownTime) Then Application.OnTime
EarliestTime:=CloseDownTime, Procedu="CloseDownFile", Schedule:=False
CloseDownTime = Now + TimeValue("00:00:10") ' hh:mm:ss
Application.OnTime CloseDownTime, "CloseDownFile"
End Sub

Public Sub CloseDownFile()
On Error Resume Next

Dim R As VbMsgBoxResult
With New IWshRuntimeLibrary.WshShell
R = .Popup("Click 'Yes' if you would like another 10 seconds... If
the 'Yes' button is not clicked Excel will save your work and close the file
in 10 seconds.", 2, , vbYesNo + vbDefaultButton2)
End With

If R = vbYes Then
' user clicked yes
Call ResetTimer
Else
' user clicked no
Application.StatusBar = "Inactive File Closed: " &
ThisWorkbook.Name
ThisWorkbook.Close SaveChanges:=True
End If
End Sub

(This code goes in ThisWorkbook Module)
Option Explicit

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
ResetTimer
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
ResetTimer
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
ResetTimer
End Sub


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Chip Pearson" wrote:

In VBA, go to the Tools menu, choose References, and then Windows
Script Host Object Model. Then, use code like


Dim R As VbMsgBoxResult
With New IWshRuntimeLibrary.WshShell
R = .Popup("Click Yes Or No", 5, , vbYesNo + vbDefaultButton2)
End With

If R = vbYes Then
' user clicked yes
Else
' user clicked no
End If

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com






On Tue, 11 May 2010 11:56:01 -0700, ryguy7272
wrote:

Sub ShutDown()
MsgBox "If there is no Activity in 5 seconds, Excel will save your work
and close. Do you need more time?", vbYesNo
On Error Resume Next
If vbYes Then
Call SetTime
Exit Sub
Else
DownTime = Now + TimeValue("00:00:05")
Application.OnTime DownTime, "ShutDown"
Call GonnaSave
End If
On Error GoTo 0
End Sub

I'm testing out some auto-close code. I'm wondering if the MsgBox actually
needs a user to click 'No' for the 'Else' part of the macro to fire. I think
so... How can I tell excel to go ahead and run the 'Else' if 'Yes' is NOT
clicked in 10-seconds?

As always, thanks!
.