Idle Timer -- Conflict with other Macros/Modules

[NCBroncos]

I know this may not enough information but this could become pretty complex. Here is what I created. I have two Userform Buttons on my worksheet which fills out a spreadsheet with whatever the user enters. Everything works great (thanks to some people on this board ). Now my issue lies in the people using the forms. They never close out of the sheet so everyone else is seeing the Read Only message. To resolve this I found the below code on the web. It does what it is created to do but it is affecting my Userforms. Can you determine what part of the code below could affect the way my Userforms work? Is there another way to close the application after a certain idle time?

If you need more info I can supply it.



In Thisworkbook -- insert code section
Private Sub Workbook_Open()
StartTimer
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
StartTimer
End Sub


In ThisWorkbook -- Module

Const idleTime = 900 'seconds
Dim Start
Sub StartTimer()
Start = Timer
Do While Timer < Start + idleTime
DoEvents
Loop
Application.DisplayAlerts = False
ActiveWorkbook.Close True
Application.DisplayAlerts = True
End Sub

thank you

 

[Jeffrey Mahoney]

Yes. A little complicated. You need to use OnTime. OnTime runs in the background for a period of time you set. It calls the subroutine you specify. The SUB needs to check if any activity has happened since the last time. This is where you have to make some decisions.

You need to create a global variable that keeps the status of changes.

You should put this in "ThisWorkbook" module

Private Sub Workbook_Open()

  Application.OnTime Now + TimeValue("00:01:00"), "CheckIfIdle", Schedule:=True


End Sub






Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  ChangeStatus = True
End Sub


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  ChangeStatus = True
End Sub


Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)


End Sub


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
  ChangeStatus = True
End Sub

This needs to be put in a standard module

Public ChangeStatus As Boolean

Standard Module

Sub CheckIfIdle()
  If ChangeStatus = False Then
    ThisWorkbook.Close SaveChanges:=True
  Else
    ChangeStatus = False
    Application.OnTime Now() + TimeValue("00:01:00"), "CheckIfIdle", Schedule:=True
  End If
End Sub

 

[Jeffrey Mahoney]

You need to decide on how much time you want the workbook to be left idle.

You need to decide by what means the Idle gets reset:
Workbook_SheetActivate gets triggered when you change to a different sheet
Workbook_SheetChange gets triggered when you manually change a cell's contents
Workbook_SheetSelectionChange gets triggered when you select a different cell on a worksheet

 

[Jeffrey Mahoney]

Be carefule when using this:

[COLOR=#333333]ActiveWorkbook.Close True[/COLOR]

If the user happens to have a different workbook activated, it may close the wrong one.

 

[NCBroncos]

Thank you both for replying so quickly I am starting to play with the below coding

Thisworkbook --
Private Sub Workbook_Open()
Reset
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Reset
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Reset
End Sub

Thisworkbook Module
Sub Reset()
Static ScheduleSave
If ScheduleSave <> 0 Then
Application.OnTime ScheduleSave, "SWork", , False
End If
ScheduleSave = Now + TimeValue("00:01:30") ' 120 seconds
Application.OnTime ScheduleSave, "SWork", , True
End Sub
Sub SWork()
ThisWorkbook.Save
ThisWorkbook.Close --> haven't changed this yet but will try other coding
End Sub

Seem to be working -- no conflicts with Userform --

 

[Jeffrey Mahoney]

Just as a reminder, can you please put your code within the CODE brackets. It is much easier to view and to copy.

 

[Jeffrey Mahoney]

Every time you make a change or select a different workbook, it will run the Reset macro. Kinda overkill. I can see that you want to reset the timer to start over each time a change happens. Oh well. If it works...