0

I am fairly new at VBA and have code in place that will close excel after inactivity. I am not sure what excel defines as inactivity and I couldn't pick up a pattern. If someone could explain this to me I would really appreciate it.

CODE IN THE "THIS WORKBOOK" SECTION

Option Explicit

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
ResetTimer
End Sub

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

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

CODE IN A STANDARD MACRO

Public CloseDownTime As Variant

Public Sub ResetTimer()
On Error Resume Next
If Not IsEmpty(CloseDownTime) Then Application.OnTime EarliestTime:=CloseDownTime, Procedure:="CloseDownFile", Schedule:=False
CloseDownTime = Now + TimeValue("00:10:00") ' hh:mm:ss
Application.OnTime CloseDownTime, "CloseDownFile"
End Sub

Public Sub CloseDownFile()
On Error Resume Next
Application.StatusBar = "Inactive File Closed: " & ThisWorkbook.Name
ThisWorkbook.Close SaveChanges:=True
End Sub
user3281827
  • 53
  • 2
  • 12
  • There is no one definition of activity for Excel. The approach you are taking seems like it will cause you no small amount of grief. I'm guessing your underlying problem is that you have a spreadsheet which is a shared resource and it can be locked by a single user. Have you considered an alternative such as Google docs which allows multi-user access? – DeanOC Mar 19 '14 at 19:59
  • I have considered it but I am using a workbook loaded with macros so it would not work. – user3281827 Mar 19 '14 at 20:05

1 Answers1

1

I'm not sure it matters more how Excel defines it or how your user would expect it to behave. From a user viewpoint, I think mouse movement and keypresses are what define activity. The absence of both would be heuristic indicators of inactivity.

Of course, the user could just be sitting there reading the sheet. A perfect method might be to install probes in the user's brain to detect if he's still paying attention. The exact steps to achieving this seem beyond the scope of this question.

EDIT: Based on your comment, I think you want to prevent the workbook from closing. Well, there are an infinite number of things you can do to prevent the workbook from closing. I think the simplest is to just delete the code in This Workbook.

Community
  • 1
  • 1
Femi
  • 1,332
  • 9
  • 20
  • it matter how excel defines it because I need to understand the methodology of why it closes. What makes excel become not idle – user3281827 Mar 19 '14 at 20:01
  • Excel does NOT close after a period of inactivity. The workbook you're using is doing that. – Femi Mar 19 '14 at 20:05
  • right the workbook closes but what does the user need to do to continue their session in the workbook – user3281827 Mar 19 '14 at 20:08
  • 1
    Based on your code, the user needs to trigger a `SheetCalculate`, `SheetChange`, or `SheetSelectionChange`. Simplest one to trigger is `SheetSelectionChange` which "Occurs when the selection changes on any worksheet. Does not occur if the selection is on a chart sheet." [http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.workbook.sheetselectionchange.aspx] – Femi Mar 19 '14 at 20:12
  • Certainly. Also, if you want the user to have more time, you can change `TimeValue("00:10:00")` to something like `TimeValue("00:30:00")` or `TimeValue("24:00:00")`. – Femi Mar 19 '14 at 20:22
  • Oh yes that would be very useful. I want to add more time thanks again! – user3281827 Mar 19 '14 at 20:23
  • Side note: if an answer helps to resolve your question, you may want to mark it as the correct answer for future visitors. – Femi Mar 19 '14 at 20:23
  • Else disabling events might do it application.EnableEvents = False in every module, worksheet or workbook object where scripts are running – pascal b Aug 26 '14 at 09:59