Timer to close excel workbook

Hi,
I was trying to make a code to close the workbook after a time period (in this code it is set to 30 sec)
It should give a warning message, before 10 sec of its closing event.
the code I have is as below.

But I am little confused that where the code to be pasted.. (in Workbook or in the module)

Please help..it is not working

Code:

---

Sub close_me()
ThisWorkbook.Close Savechanges:=True
End Sub


Sub close1_me()
MsgBox "Please update the file. The file will close with in 10 sec."
Application.OnTime Now + TimeValue("00:00:10"), "close_Me"
End Sub

 
Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:00:30"), "close1_Me"
End Sub

---

Hi Leo,

Welcome to ExcelFox !!!

Place the Workbook_Open code in the Workbook module. Place other two subs in a standard module.

HTH

Hi,

Or you could even try this, which automatically closes the MsgBox.

Code:

---

Sub close1_me()
   
    Dim objWsh          As Object, msg As String
    Const Delay        As Long = 3 'secs ?
    Const wButtons      As Long = 16 ' Boutons + icon
   
    Set objWsh = CreateObject("WScript.Shell")
    msg = "Please update the file. The file will close within 10 sec."
   
    objWsh.Popup msg, Delay, "ExcelFox", wButtons
    Application.OnTime Now + TimeValue("00:00:10"), "close_Me"

    Set objWsh = Nothing

End Sub

---

Hi..
Thank you .. It works...Gr8

Hi,

These codes are working smoothly with 2003 version excel workbook.. is there any alternate code for 2007?

otherwise i have to make the file with macro enabling...

but i m not prefering to keep the file as macro enabled format...

Thanks in advance..

Hi,

Without enabling the macro the code won't work either in 2003. I guess your security settings in Excel 2003 is set to Low.

In XL 2007 you can do this by Click on Office button > Excel Options > Trust Center > Macro settings > check on disable all macros without notification (though it's not recomended )