CODEWITHSUNDEEP
windowsvbaexcel
Tim Wilkinson
Tim Wilkinson

Reputation: 3801

VBA to close excel file when Windows7 computer is locked

I have an excel file on a shared drive used by 6/7 people, however only one person can edit at a time. The frustration comes when one person opens the file for editing then disappears for lunch for an hour, leaving the excel file open and un-editable for other users.

Is it possible for VBA to listen for when a station is locked, and activate a macro accordingly?

Sorry I am not posting any of my own attempts as I'm a bit of a fish out of water with this level of VBA.

Any points that may help get me started would be really useful.

Upvotes: 3

Views: 905

Answers (1)

Ben Rhys-Lewis
Ben Rhys-Lewis

Reputation: 3246

You have a few options:

  1. Kill the co-workers who do this
  2. Have other users create a copy and save-as to then merge latter (quite hacky)
  3. Or you try a timeout - so if the user selects nothing i 10 minutes the workbook closes. Selecting lock would be a issue with security I think and windows wouldnt let you have that kind of power.

So to timeout call a function every ten minutes to check if user has selected any other cells in the worbook.

If difference("n", lastaction , Now) > 10 Then
    ThisWorkbook.Close SaveChanges:=True
End If

You can use NOW function in vba to find current date and time and the work out difference with when an action was made to find the value of 'lastaction'. To do this use:

Sub AnAction(ByVal Sh As Object, ByVal Target As Range)
    lastaction = now
End Sub

Hopefully that answers your question.

Upvotes: 1

Related Questions