+ Reply to Thread
Results 1 to 5 of 5

Application.Ontime to close Excel when inactive

  1. #1
    Registered User
    Join Date
    02-18-2020
    Location
    Kentucky
    MS-Off Ver
    365
    Posts
    2

    Application.Ontime to close Excel when inactive

    Hey all - I am using Application.Ontime to close workbook automatically if a cell value doesn't change for a set amount of time. I am having an issue with the Ontime procedure still running once file is closed. I have been looking around for similar issues, but all seem to just be related to code not using the exact same time/format to canacel the action. Pretty sure using the public variable as below is sufficient.

    Code for file startup (in This Workbook code)

    Please Login or Register  to view this content.
    Initiate Application.Ontime. Time set for 2 min for testing (In Module 1)

    Please Login or Register  to view this content.
    If cell value changes, cancel the procedure, and start again (In This Workbook code)

    Please Login or Register  to view this content.
    Procedure to cancel the Ontime action (in Module 1)

    Please Login or Register  to view this content.
    Procedure that is called ( in Module 1)

    Please Login or Register  to view this content.
    I also call StopTheTime in the BeforeSave procedure in the worbook in case it is closed manually, but issue occurs when file is manually or automatically closed.

    Some observations:
    1.) If no change is made to the document, the code works as intended. If once change is made, the file closes after the set amount fo time, but as long as the application is open, it continues to reopen the document and run procedure and doesn't seem to stop. the one change should only initialize the Application.Ontime 1 additional time I would think, but as far as I can tell, there is no end (at least I didn't care to keep counting after ~20 interations).
    2.) If I change "TimeToClose" variable while workbook is open (lets say from 2 minutes to 5 minutes), then change the document, then it resets the timer successfully for 5 minutes, but continues to reopen and close the document every 5 minutes. So this would make me think the cancel is successful.

    I am probably overlooking something simple. Ideas?

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,653

    Re: Application.Ontime to close Excel when inactive

    Call StopTheTime in the BeforeClose workbook procedure
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    02-18-2020
    Location
    Kentucky
    MS-Off Ver
    365
    Posts
    2

    Re: Application.Ontime to close Excel when inactive

    Sorry - I made a note in my description but didn't include the code. I noted i called StopTheTime in BeforeSave, but I actually used BeforeClose. I should have included the code to prevent confusion.
    As you can see, copying my code directly with comments, I have tried several ways when closing the document, as well as call StopTheTime several times (tried making two changes and calling it twice, etc.)

    Still having issues, unfortunately.
    Please Login or Register  to view this content.

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,653

    Re: Application.Ontime to close Excel when inactive

    This is a guess, but try calling StopTheTime as the 1st line in TimeToClose .

  5. #5
    Valued Forum Contributor
    Join Date
    11-26-2012
    Location
    Sydney
    MS-Off Ver
    2010
    Posts
    423

    Re: Application.Ontime to close Excel when inactive

    Unfortunately, I'm not spotting anything wrong either. In better news, I did do something very much like this a while ago (except I closed without saving). I even posted the code here trying to help someone else. Maybe try adjusting that - hope it works for you!
    https://www.excelforum.com/excel-pro...ml#post4942466
    Last edited by mgs73; 03-09-2020 at 05:48 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA - Application.Ontime - When Application Close
    By saravnepali in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-22-2019, 09:29 AM
  2. VBA - Application.Ontime Cancel Fails to Method 'ONTIME' of Object 'Application'
    By LordAzuRa in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-10-2016, 03:21 PM
  3. [SOLVED] Close userform if inactive and the application
    By boboivan in forum Excel General
    Replies: 4
    Last Post: 02-04-2015, 12:53 PM
  4. Close Workbook with Application.OnTime
    By dylanemcgregor in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 07-30-2009, 11:52 AM
  5. execute macro; save it and close using Application.OnTime
    By ina in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-12-2006, 12:25 PM
  6. Close excel when inactive
    By jennie in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-09-2006, 04:45 PM
  7. Help .. stopping Application.Ontime when you close a workbook
    By L2B in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-13-2005, 06:43 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1