OnTime Function opening my workbook?

[Bozacke]

Hello,

I'd like to know what exactly the following script does?

Private Sub Workbook_Open()
Application.OnTime TimeValue("06:00:00"), "RunMeCloseIt"
End Sub

I assumed that if the specific file was open at 6AM it would automatically run the macro "RunMeCloseIt" (which closes the file and saves a back-up with the user name and date in time in the file name in a different folder). I created this because the file I've put this into is used by multiple people and sometimes they forget to close the file which interferes with one of my scheduled tasks that updates the file every morning.

But apparently Private Sub Workbook_Open() opens the workbook at 6AM and then runs the script "RunMeCloseIt" which closes the file. I thought that Private Sub Workbook_Open() would only execute "RunMeCloseIt" if the file containing these scripts is open, but sometimes it seems to be opening the file at 6AM to run this script? Strangely though, Private Sub Workbook_Open() doesn't open up the file and run all the time? It appears that if I close Excel and then re-open Excel the file will not open itself at 6AM, but if the file was recently opened and then closed it Private Sub Workbook_Open() seems to open the file at 6AM, but still not all the time? I've now learned that When using OnTime you need to cancel it or it will continue to run.

Since all I want to do is to close the file at a specific time if it's open am I using the wrong commands and is there someting other than the Application.OnTime command I can use and if not how can I turn off the OnTime command?

Boz

[Andrew Poulsom]

When you open the workbook the OnTime event is set to run the procedure RunMeCloseIt at 6AM. If you close the workbook, but leave Excel open, the Ontime event is still set. When 6AM arrives, Excel opens the workbook in order to run the procedure.

What you need to do is cancel the OnTime event when you close the workbook, like this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.OnTime EarliestTime:=TimeValue("06:00:00"), Procedure:="RunMeCloseIt", Schedule:=False
End Sub

[Bozacke]

Andrew,

Thank you!!! That does the job!!!

Boz