Worksheet inactive timer with a form

cdwr0550

New Member
Joined
Aug 30, 2017
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code:
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
VBA Code:
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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Have you tried just putting the start stop code into the userform code like this:
VBA Code:
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
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
Solution
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
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top