MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Macro Timer


Posted by Dan on September 18, 2001 9:15 AM

Does the ability exist in Excel 97 to have a macro that closes and saves a spreadsheet after a fixed period of inactivity.


Posted by Damon Ostrander on September 20, 2001 5:10 AM

Hi Dan,

Yes this can be done fairly easily using the OnTime method to perform a periodic check (say every 10 seconds) if inactivity has persisted too long. You would need to create a public variable that contains the time you want the save and close to occur. This time would be updated to the current time + x (where x is the max allowed period of inactivity) each time an event occurs that indicates activity. You would probably want to use the workbook events SheetChange, SheetSelectionChange, SheetActivate, WindowActivate, etc., to update this time. The OnTime method would then call a routine every 10 seconds to check if the current time is past the expire time. If not it reschedules itself 10 seconds in the future, if so it saves and closes.

Happy computing.

Damon