Timer to close excel workbook

[leopaulc]

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




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

[Admin]

Hi Leo,

Welcome to ExcelFox !!!

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

HTH

[Admin]

Hi,

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


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

[leopaulc]

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

[leopaulc]

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

[Admin]

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 )