Microsoft Excel - Time out shell message box not closing

Asked By John Wirth on 18-Mar-14 05:52 AM
I am working with a pop up box that should auto time out after five seconds- unfortunately, it doesn't:

Public Sub Chchchchanges()
On Error Resume Next
Dim cTime As Long
Dim WSH As Object
WSH = vbSystemModal
Set WSH = CreateObject("WScript.Shell")
cTime = 5
Select Case WSH.Popup("File ready to auto save" & vbCrLf & vbCrLf & "Click OK to cancel" & vbCrLf & vbCrLf & "This pop up will close itself in 5 seconds and auto save will go ahead" & vbCrLf & vbCrLf & "If you close this message using the 'X', auto save will still go ahead", cTime, "Auto save notification for log on & patrol sheet", vbSystemModal)
Case vbOK
Case -1
ThisWorkbook.Save
Case Else
ThisWorkbook.Save
End Select
End Sub


Any help would be appreciated.
Harry Boughen replied to John Wirth on 18-Mar-14 07:08 AM
Hi John,
I have put your code into a workbook and it appears to be working except that it does not save when you close with the X.  Is that what you mean the problem is?
Regards
Harry
Harry Boughen replied to John Wirth on 18-Mar-14 07:27 AM
Hello again John,
Try this
Public Sub Chchchchanges()
On Error Resume Next
Dim cTime As Long
Dim WSH As Object
WSH = vbSystemModal
Set WSH = CreateObject("WScript.Shell")
cTime = 5
Select Case WSH.Popup("File ready to auto save" & vbCrLf & vbCrLf & "Click OK to cancel" & vbCrLf & vbCrLf & "This pop up will close itself in 5 seconds and auto save will go ahead" & vbCrLf & vbCrLf & "If you close this message using the 'X', auto save will still go ahead", cTime, "Auto save notification for log on & patrol sheet", vbSystemModal)
'Case vbOK
Case 1, -1
ThisWorkbook.Save
Case Else
ThisWorkbook.Save
End Select
End Sub
Regards
Harry
Harry Boughen replied to John Wirth on 18-Mar-14 04:32 PM
Hello John,
It was late at night and I didn't test that altered code sufficiently.  It always saves the file.
It seems that when you close the popup with the X it outputs as if the OK was pressed.
The only other thing I can think of is to put another button on it and make that the default and maybe that value will be able to be detected if it is closed with the X.
When i get a minute in a while I will fiddle some more.
Harry
Harry Boughen replied to John Wirth on 18-Mar-14 04:59 PM
Hello again John,
I am pretty sure this works with the second button as the default.

Public Sub Chchchchanges()
On Error Resume Next
Dim cTime As Long
Dim WSH As Object
WSH = vbSystemModal
Set WSH = CreateObject("WScript.Shell")
cTime = 5
Select Case WSH.Popup("File ready to auto save" & vbCrLf & vbCrLf & "Click OK to cancel" _
    & vbCrLf & vbCrLf & "This pop up will close itself in 5 seconds and auto save will go ahead" _
    & vbCrLf & vbCrLf & "If you close this message using the 'X', auto save will still go ahead" _
    , cTime, "Auto save notification for log on & patrol sheet", 1 + 256 + 4096)
Case vbOK
Exit Sub
Case -1
ThisWorkbook.Save
Case Else
ThisWorkbook.Save
End Select
End Sub

Hope I haven't lead you up the garden path.
Harry
John Wirth replied to Harry Boughen on 02-Apr-14 03:46 AM
Thank you very much Harry.
I've tried the code and it works perfectly. Trouble is, it's in a workbook that has a lot of code in various modules, and in that context, the pop up doesn't close after the five seconds. As there's so much code and I don't know which part of it is causing the conflict (if indeed that is the problem), there doesn't seem to be any point in posting. I will have a look and disable all other code and gradually reintroduce sub by sub until I identify which one it is and then post back.

Thank you kindly for your time.