Auto Close Excel Workbook when Inactive Period Expires - Problem

[loki32loki]

Hello everyone,

I have code that allows me to close and save a workbook when 10 minutes of inactivity takes place.

The code has a 10 minute timer that expires bringing up a SubForm with a 15 second timer allowing the user to reset the timer. This all works fine.

If the user does their work and closes out of the file prior to the 10 minute timer expiring everything works great.

The problem I am having is when the SubForm gets engaged and the user resets the primary timer.

It appears that there person can complete their work, close out of the file, and if the Excel Application is still up the system is not disengaging the timer from the reset and it attempts to reopen the spreadsheet.

The user can reopen the sheet and just close out and the situation resolves itself, however I am trying to figure out how to get the reset timer to actually close and avoid this problem.


Here is the code I have (this is completely someone else's work and though I can follow the code and how it processes, I cannot figure out how to disengage the primary timer reset).

ThisWorkbook

Private Sub Workbook_Open()
'This activates the StartTimer code
StartTimer
End Sub
 
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
 
' Check to see if the UserForm Timer is active.
' If it is, it has control, just exit this event without doing anything.
If Timer2Active = True Then Exit Sub
Application.OnTime BootTime, "CloseBook", , False
StartTimer
End Sub
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'This will stop the timer and close the worksheet.
Application.OnTime BootTime, "CloseBook", , False
End Sub

Forms

Private Sub UserForm_Activate()
' Set the Timer2Active Flag to True.
' This stops the SheetSelectionFlag from processing it's code
' This UserForm now has control over what will be done next.
Timer2Active = True
' Start a timer within this UserForm. If the allotted time elapses
' without pressing the CommandButton, call "ReallyCloseBook"
Application.OnTime Now + TimeValue("00:00:15"), "ReallyCloseBook"
End Sub
Private Sub CommandButton1_Click()
' Set the Timer2Active flag to False in order that the
' SheetSelectionChange Event can monitor activity.
Timer2Active = False
' Restart the Main timer again
StartTimer
' Dispense with the UserForm
Unload UserForm1
End Sub

Modules

Public BootTime As Date
Public Timer2Active As Boolean
Sub StartTimer()
' Start the Activity Timer; in this example 30 seconds for testing
'purposes only; normally this code would be 10 minutes
BootTime = Now + TimeValue("00:00:30")
' If the Activity Timer runs for the specified time
' call the "CloseBook" sub
Application.OnTime BootTime, "CloseBook"
End Sub
Sub CloseBook()
' At this point the main Timer has elapsed.
' Display and let the UserForm take charge.
UserForm1.Show
End Sub

Any thoughts or assitance on this would be gratefully received.

 

[tusharm]

It's kinda hard to tell what is happening. There are too many timed procedures being called and some that seem to be missing (reallycloseworkbook).

My guess is that you have pending timed procedures. When you really close the workbook, make sure you cancel all pending procedures. For more on how to do that see the Excel VBA help for the OnTime method. One of the parameters lets you cancel a pending timed procedure.

You may also want to look at
How do I close a workbook that hasn't been used in 5 minutes? (streamlined)
http://www.tmehta.com/tmxl/dbfind_byid.asp?id=7

Hello everyone,

I have code that allows me to close and save a workbook when 10 minutes of inactivity takes place.

The code has a 10 minute timer that expires bringing up a SubForm with a 15 second timer allowing the user to reset the timer. This all works fine.

If the user does their work and closes out of the file prior to the 10 minute timer expiring everything works great.

The problem I am having is when the SubForm gets engaged and the user resets the primary timer.

It appears that there person can complete their work, close out of the file, and if the Excel Application is still up the system is not disengaging the timer from the reset and it attempts to reopen the spreadsheet.

The user can reopen the sheet and just close out and the situation resolves itself, however I am trying to figure out how to get the reset timer to actually close and avoid this problem.


Here is the code I have (this is completely someone else's work and though I can follow the code and how it processes, I cannot figure out how to disengage the primary timer reset).

ThisWorkbook

Private Sub Workbook_Open()
'This activates the StartTimer code
StartTimer
End Sub
 
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
 
' Check to see if the UserForm Timer is active.
' If it is, it has control, just exit this event without doing anything.
If Timer2Active = True Then Exit Sub
Application.OnTime BootTime, "CloseBook", , False
StartTimer
End Sub
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'This will stop the timer and close the worksheet.
Application.OnTime BootTime, "CloseBook", , False
End Sub

Forms

Private Sub UserForm_Activate()
' Set the Timer2Active Flag to True.
' This stops the SheetSelectionFlag from processing it's code
' This UserForm now has control over what will be done next.
Timer2Active = True
' Start a timer within this UserForm. If the allotted time elapses
' without pressing the CommandButton, call "ReallyCloseBook"
Application.OnTime Now + TimeValue("00:00:15"), "ReallyCloseBook"
End Sub
Private Sub CommandButton1_Click()
' Set the Timer2Active flag to False in order that the
' SheetSelectionChange Event can monitor activity.
Timer2Active = False
' Restart the Main timer again
StartTimer
' Dispense with the UserForm
Unload UserForm1
End Sub

Modules

Public BootTime As Date
Public Timer2Active As Boolean
Sub StartTimer()
' Start the Activity Timer; in this example 30 seconds for testing
'purposes only; normally this code would be 10 minutes
BootTime = Now + TimeValue("00:00:30")
' If the Activity Timer runs for the specified time
' call the "CloseBook" sub
Application.OnTime BootTime, "CloseBook"
End Sub
Sub CloseBook()
' At this point the main Timer has elapsed.
' Display and let the UserForm take charge.
UserForm1.Show
End Sub

Any thoughts or assitance on this would be gratefully received.

 

[loki32loki]

Hi Tushar Mehta your are correct that was my error in copying the code from the file the missing information is

Sub ReallyCloseBook()
If Timer2Active = False Then Exit Sub
Unload UserForm1
ThisWorkbook.Save
' Application.Quit
ThisWorkbook.Close
End Sub

Which is stored in Module 1 right under the CloseBook() segment. Sorry for not providing the whole picture. As I stated the process works up to the point where the user has to extend the time and then exit. The system attempts to restart the file.
Again any help would be appreciated. I did review the data prior to posting and can't seem to figure out how to get around this glitch.