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

mvan231

New Member
Joined
Oct 18, 2011
Messages
6
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top