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

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''.

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

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''.

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

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!

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

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!
.