Worksheet inactive timer with a form

[cdwr0550]

I have a worksheet with an inactive timer that checks for user activity on the worksheet. However, I changed the method of how data is entered to a userform and now when a user opens the form I built it counts the time in the form as inactivity on the worksheet. How to fix my timer to incorporate my entry from.

Below is my timer code:

in Thisworkbook

Private Sub Workbook_Open()

MsgBox ("The data entry log is set to save and close in 5 minutues of inactivity")


Call StartClock
End Sub



Private Sub Workbook_SheetCalculate(ByVal sh As Object)

Call StopClock

Call StartClock

End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Call StopClock

Call ProtectAllWorksheets
    
    
    
    ActiveWorkbook.Save

    
End Sub


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

Call StopClock

Call StartClock

End Sub

in Module

Public NoActivity As Date
Sub StopClock()

On Error Resume Next

Application.OnTime NoActivity, "ShutDown", , False

End Sub
Sub StartClock()

NoActivity = now + TimeValue("00:05:00")

Application.OnTime NoActivity, "ShutDown"

End Sub

Sub ShutDown()

Application.DisplayAlerts = False

With ThisWorkbook
.Save
.Close
End With

End Sub

 

[offthelip]

Have you tried just putting the start stop code into the userform code like this:

Private Sub UserForm_Click()
Call StopClock

Call StartClock

End Sub

Obviously you could use more than just the click event on the useform to detect activity and put it in lots of events

 

[cdwr0550]

I tried to enter it in the userform_activate event and it still closes the worksheet even tho user is still active using the form

 

[offthelip]

I think you need to put it in more events that just the userform_activate event, that only happens when the userform appears, you need to put it any of the events that your userform uses, and also you may need to put it in the code of any of the controls you have on the userform. Actually I can't think of any reason why you can't put that in EVERY event on the userform, ( apart from taking a while to do it)

 

[cdwr0550]

Awesome, thanx for the advise offhelip. Dont know why I didnt think of that in the first place. Sometimes the most obvious solution are the simplest ones but hard to find