Results 1 to 8 of 8

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

This is a discussion on Macro to close after certain period of time still runs after workbook closed within the Excel Questions forums, part of the Question Forums category; This is my first post on the forum and I'd like to say thanks for all previous help I've received ...

  1. #1
    New Member
    Join Date
    Oct 2011
    Posts
    6

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

    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

  2. #2
    Board Regular
    Join Date
    Jan 2010
    Location
    UK, Shropshire, Shrewsbury
    Posts
    469

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

    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

  3. #3
    New Member
    Join Date
    Oct 2011
    Posts
    6

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

    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?

  4. #4
    New Member
    Join Date
    Oct 2011
    Posts
    6

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

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

  5. #5
    Board Regular
    Join Date
    Jan 2010
    Location
    UK, Shropshire, Shrewsbury
    Posts
    469

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

    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/libr...ffice.11).aspx

  6. #6
    New Member
    Join Date
    Oct 2011
    Posts
    6

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

    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

  7. #7
    New Member
    Join Date
    Oct 2011
    Posts
    6

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

    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

  8. #8
    New Member
    Join Date
    Oct 2011
    Posts
    6

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

    sorry to continue posting but I believe this is what I need.
    http://www.pcreview.co.uk/forums/can...d-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.

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com