CODEWITHSUNDEEP
excelvba
EasyOne
EasyOne

Reputation: 25

Timer of inactivity in Excel VBA

I have created an Excel file that is shared via local Onedrive.

The file is locked if any user opens it. How to automatically close the file after 5 minutes, but not faster than 2 minutes after last use?

The Sleep function is not what I am looking for, because I don't want to freeze the file but rather something like a timer to call once in a while.

In Javascript it would be something like

activityTimer = setTimeout(turnoff, 300000)

In other parts of script it would be :

clearTimeout(activityTimer); 
activityTimer = setTimeout(turnoff, 120000)

Can I do something similar in VBA?

Upvotes: 1

Views: 2014

Answers (1)

Sorceri
Sorceri

Reputation: 8033

Yes, you could do this using Application.OnTime (although probably not recommended)

You would need a module to hold a Global Variable for the date object You would need to set the Global variable on Workbook.Open You would need to reset your Global Time after each change to the workbook and there are a lot of events. The example below shows how to accomplish this by using the Worksheet Change event in the ThisWorkbook module.

In the Module you would declare

Public GlobalTimer As Date

Public Function CheckTimer() As Boolean
'check to see if we have a 5 minute interval
If DateDiff("n", GlobalTimer, Now()) >= 5 Then
    'do your close excel stuff
Else
    'check again in x amount of minutes - currently it is 2 minutes
    Application.OnTime Now() + TimeValue("00:02:00"), "CheckTimer"
End If
End Function

in the Thisworkbook Module

Private Sub Workbook_Open()
    'set the variable to the current time
    GlobalTimer = Now()
    'Opening workbook so check in 5 minutes
    Application.OnTime Now() + TimeValue("00:05:00"), "CheckTimer"
End Sub



Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    'the workbook changed so reset the time
    GlobalTimer = Now
End Sub

Upvotes: 1

Related Questions