MsgBox; if 10-seconds, assumed 'No' Clicked

[ryguy7272]

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!

 

[Mike H]

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/library/x83z1d9f(VS.85).aspx
--
Mike

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

 

[Chip Pearson]

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

 

[ryguy7272]

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, Procedure:="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