I had this similar problem. I have some code that someone on this board helped me with (I'm sorry, i dont remember who
) shuts the worksheet down after it hasnt been used for a certain time, and eventhough the sheets were being closed, after the set time, they opened and closed again.
The problem youre having is that the code is still being run eventhough the file is closed. To stop this, you need to disable the counter. Im not the best person to tell you exactly how to do that but, heres the code I use:
In "ThisWorkbook" I have this code:
'This disables the time coutner and resets it when a caculation is made
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Call Disable
Call SetTime
End Sub
'This disables the time counter and resets it when a sheet is changed
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Call Disable
Call SetTime
End Sub
and in a module, i have this code:
Dim DownTime As Date
'This is the code that the macros in ThisWorkbook calls to set the timer
Sub SetTime()
DownTime = Now + TimeValue("00:15:00")
Application.OnTime DownTime, "ShutDown"
End Sub
'This is the code that the macros in ThisWorkbook calls to close the file
Sub ShutDown()
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub
'This is the code that the macros in ThisWorkbook calls to disable the timer
Sub Disable()
On Error Resume Next
Application.OnTime EarliestTime:=DownTime, Procedure:="ShutDown", Schedule:=False
End Sub
Im assuming youll need something similar to the Disable code.
Hope this helps some