Excel Time out issue

Question

Hello All,

I have code on how to make an excel file close automatically  when idle for a specific time. However, the challengeI am facing is although the workbook I have this code in automatically timesout and closes, if there is another excel file (without this code) also open, then for some reason automatically my original workbook with the below code keeps opening and closing every 2 mins (Time out period I mentioned).

This only happens so long as Microsoft Excel is open. If I close all workbooks open, then it does not open again. However, I don't want to put Application.Close and kill excel for this. I want a better solution. Kindly let me know you thoughts?

Thanks

Ajay N

CODE:

---

Module 1:

---

Public NoActivity As Date

Public Sub ShutDown()

   On Error Resume Next

   Application.DisplayAlerts = False

   With ThisWorkbook

       .Save

        .Close

    End With

End Sub

Public Sub StartClock()

   On Error Resume Next

   NoActivity = Now + TimeValue("00:02:00")

   Application.OnTime NoActivity, "ShutDown"

End Sub

Public Sub StopClock()

   On Error Resume Next

   Application.OnTime NoActivity, "ShutDown", , False

End Sub

Workbook Module:

---

Private Sub Workbook_Open()

Call StartClock

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Call StopClock

End Sub

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

Call StopClock

Call StartClock

End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

Call StopClock

Call StartClock

End Sub

Answer 1

Any suggestions?

Unfortunately suggestions is all I can offer because I tested my previous post on a dummy workbook with another workbook open and it worked without re-opening when other workbooks were open. However, I did not have any calculations taking place so I wonder if your workbook is calculating before it saves and as a result, it calls the work calculate event and turns the timer back on. Try using a public Boolean variable to suppress the calculate event when it calls the shutdown. (It won't suppress calculating; just suppress the calculate event code).

Public NoActivity As Date

Public SuppressCalcEvent As Boolean

Public Sub ShutDown()

    On Error Resume Next

    Application.DisplayAlerts = False

    SuppressCalcEvent = True

Call StopClock

    With ThisWorkbook

        .Save

        .Close

    End With

End Sub

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

    If SuppressCalcEvent Then Exit Sub

    Call StopClock

    Call StartClock

End Sub

Answer 2

Hi OssieMac,

I have 1 last question. I want to constantly show the countdown time until the timeout period of inactivity in the excel status bar.

How do I do this? I tried below but obviously this will not work because it will update only when I touch the excel which I don't want to count inactivity.

Code:

---

Public Sub Counter()

Dim bln As Boolean

bln = Application.DisplayStatusBar

Application.DisplayStatusBar = True

Application.StatusBar = "Unused for " + Format((Now - NoActivity), "hh.mm.ss")

End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Call Counter()

End Sub

Answer 3

Hello OssieMac,

Thank you so much. You are awesome! This solution worked for me. When the calculation is suppressed it does not restart again :).

Thank you again.  You saved my day :)..

Answer 4

Hi OssieMac,

Thank you for your reply. I tried this approach. But could not get this to work unfortunately. It still keeps opening up. Any suggestions?

Thanks

Answer 5

Try Calling StopClock in ShutDown instead of in the Close Event.

I suspect that the clock is not getting properly stopped and this should ensure that it is turned off.

Public Sub ShutDown()

    On Error Resume Next

    Application.DisplayAlerts = False

    Call StopClock

    With ThisWorkbook

        .Save

        .Close

    End With

End Sub