Close an excel workbook opened by another user on the same computer using VBA

Question

I have been working on an excel document for making employee schedules for my work, using excel 2010. One issue that we constantly have is that a user may have the workbook open and forget to close it before they leave. They are still logged into the computer (a shared computer) but that user profile is locked, and another user logs in. The file in use is stored on a network drive, so it could be locked on different computers, or the same computer the current user that wants to access it is using. Of course, if the workbook was left open, the new user can't make changes. I was wondering if there is a way to add some code to the document so I could put a button that would close the instance of the workbook that is still open by another user, perhaps giving the current user the option to save it or not, then close and reopen the workbook that the current user opened so they can gain access to make changes? Let me know if I need to clarify anything for you. Thank you!

Answer 1

You can do some trick. The main idea is to close workbook if nobody has changed the selection of cells (i.e. select A1 or another cell) in example for 10 minutes.

1) add this code to the vba module:

Public lastSelectionChange As Date

Sub closeWB()
    If DateDiff("n", lastSelectionChange, Now) > 10 Then
        ThisWorkbook.Close SaveChanges:=True
    Else
        Application.OnTime Now + TimeSerial(0, 10, 0), "closeWb"
    End If
End Sub

2) add following code to the ThisWorkbook module:

Private Sub Workbook_Open()
    lastSelectionChange = Now
    closeWB
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    lastSelectionChange = Now
End Sub

Function closeWb will called every 10 minutes (Application.OnTime Now + TimeSerial(0, 10, 0), "closeWb" do this) and check if last selection change was over 10 minutes ago. If so, then close wb.

Answer 2

That would be a very serious security issue in te operation system. Being able to interact with another users' files and running programs.

However, you could have the current user do a "Save-as" or create a copy and continue from there

Answer 3

I think I have a solution. You place a timer code for e.g. every 30s which searches a specific folder for a specific file/file name. If the file doesn't exist, then do nothing. If the file exists then it closes the workbook without saving changes. To start the shut down procedure on a peers PC that has this workbook open then you create a separate macro that creates that specific file and deletes it just before shutdown. You have to make sure that the shutdown procedure doesn't work for read only copies of the workbook. Alternatively you could manually paste the file there and delete once the master workbook is closed.