Macro to close after certain period of time still runs after workbook closed

[mvan231]

This is my first post on the forum and I'd like to say thanks for all previous help I've received from previous posts about various things.

On to what I need help with.

I have a macro set up in a sheet to run when the workbook opens. Here is the code:

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

I have placed this under "ThisWorkbook"

This works fine. The macro it is linked to is:

Sub closebookdelay()
Workbooks("Untitled1.xls").Close True
End Sub


This also works fine.

Here is my problem. I typically do not hit the 5 minute limit because I usually close the workbook before hand. The limit is for others that might be viewing the sheet. I usually have other workbooks open when I am viewing this one. If I close the workbook manually instead of waiting for the timer it seems that the macro is opening the workbook again and closing it. Any idea why this would be happening?

Thanks for your help in advance

 

[obiron]

Welcome to the board.

The answer is in the question.

You have told the Application (Excel) that you want to run the macro in the workbook at a certain time. Until it runs the macro, it won't know what it does so it will have to open the workbook to close it.

You could create a personal.xls (google it...) and put the macro in there. If you were going to do this I would pass the workbook name in as an argument and get the macro to check if the workbook was in the workbooks collection before closing it - this obviously doesn't help if you are distributing the workbook and not the personal.xls

 

[mvan231]

Yes, the file will be stored on a network drive for multiple people to access and find information. What I am wondering is if for some reason the macro timer is still running after the workbook is closed?

and if this is the case is there a way to cancel the timed macro when the workbook is closed?

 

[mvan231]

Seems I have found my answer here: http://www.ozgrid.com/Excel/run-macro-on-time.htm

 

[obiron]

You learn something new every day...

I found that Ozgrid article a bit confusing so I will try to re-interpret what they were saying.

The appliction.OnTime function takes four arguments:
->The earliest time to start
->The procedure to run
->The latest time to start in the event that Excel is not in a ready state at the start time (optional - if not supplied, no latest time is set)
->Whether to set or cancel the schedule (optional - if not supplied then assumed to be setting a schedule)

Normally you pass the earliest time as a specific time or Now() + a time interval. By creating a public variable (dTime) outside of a procedure and then updating it inside the procedure that is scheduled, you can continually make calls to the scheduler. In addition as part of the workbook_close event, you can unschedule the last schedule because you know what dTime is

The MSDN link is here

http://msdn.microsoft.com/en-us/library/aa195809(office.11).aspx

 

[mvan231]

Thanks for your help. that article was confusing and I was having an issue with the VBA macro and excel.

Would this work for a workbook_open event?:

Private Sub Workbook_Open()

Application.OnTime Now + TimeValue("00:05:00"), "closebookdelay", , False

End Sub

 

[mvan231]

Figured it out I think.

Please consider this and let me know if this would be correct:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnTime EarliestTime:=Now + TimeValue("00:05:00"), Procedure:="closebookdelay", schedule:=False

End Sub

Private Sub Workbook_Open()

Application.OnTime EarliestTime:=Now + TimeValue("00:05:00"), Procedure:="closebookdelay", schedule:=True

End Sub

The macro "closebookdelay" just simply closes the workbook and saves changes

 

[mvan231]

sorry to continue posting but I believe this is what I need.
http://www.pcreview.co.uk/forums/canceling-macro-has-been-scheduled-t948778.html

To cancel an OnTime macro, you must specify the *exact* time the
macro is scheduled for. Therefore, instead of code like

Application.OnTime Now + TimeValue("00:00:15"), "my_Procedure"

You should store the time in a global variable, and use that with
OnTime. E.g.,

Dim RunWhen As Double
Sub StartTimer()
RunWhen = Now + TimeValue("00:00:15")
Application.OnTime RunWhen, "my_Procedure"
End Sub

Then, you can use the RunWhen to cancel the procedure:

Application.OnTime RunWhen,,,False

See www.cpearson.com/excel/ontime.htm for more details.