Identifying the username that is locking a file for editing

Re: Identifying the username that is locking a file for editing

Here's some code that I have had lying around. Can't remember where it's from . What it should do is close the workbook after 10 minutes of idle time.

'First of all set a time limit eg 10 minutes 
Private Sub Workbook_Open() 
    EndTime = Now + TimeValue("00:10:00") 
    RunTime 
End Sub 


'This resets the timer if a change in the workbook occurs
 
Private Sub Worksheet_Change(ByVal Target As Range) 
    If EndTime Then 
        Application.OnTime _ 
        EarliestTime:=EndTime, _ 
        Procedure:="CloseWB", _ 
        Schedule:=False 
        EndTime = Empty 
    End If 
    EndTime = Now + TimeValue("00:10:00") 
    RunTime 
End Sub 


'If no changes have occurred then this runs 
Public EndTime 
Sub RunTime() 
    Application.OnTime _ 
    EarliestTime:=EndTime, _ 
    Procedure:="CloseWB", _ 
    Schedule:=True 
End Sub 
 
Sub CloseWB() 
    Application.DisplayAlerts = False 
    With ThisWorkbook 
        .Saved = True 
        .Close 
    End With 
End Sub

Re: Identifying the username that is locking a file for editing

Hi Roy,

Where does this code go? Does it go in "This workbook" ? And Is there any specific keys to hit to get it to work?

Thanks,

Larry

Re: Identifying the username that is locking a file for editing

Yes the code would in the "This Workbook" section. You don't have do anything. When the workbook is opened the code will fire, henc Workbook_Open()

The Worksheet_Change() will fire as soon as the worksheet is changed in any way. Like a value being edited.

Re: Identifying the username that is locking a file for editing

I must be doing something wrong. I pasted the code into the "This Workbook" the only thing I changed was the time to 1 minute "00:01:00". Is there something I'm missing?

Private Sub Workbook_Open()
    EndTime = Now + TimeValue("00:01:00")
    RunTime
End Sub
 
 
Private Sub Worksheet_Change(ByVal Target As Range)
    If EndTime Then
        Application.OnTime _
        EarliestTime:=EndTime, _
        Procedure:="CloseWB", _
        Schedule:=False
        EndTime = Empty
    End If
    EndTime = Now + TimeValue("00:01:00")
    RunTime
End Sub


 
Public EndTime
Sub RunTime()
    Application.OnTime _
    EarliestTime:=EndTime, _
    Procedure:="CloseWB", _
    Schedule:=True
End Sub
 
Sub CloseWB()
    Application.DisplayAlerts = False
    With ThisWorkbook
        .Saved = True
        .Close
    End With
End Sub

Thanks,

Larry

Re: Identifying the username that is locking a file for editing

That's my fault..The Workbook_Open() should be placed in the "This Workbook" aea. The rest will be placed in the actual worksheet module (Sheet1 or Sheet 2, and so on.)

Re: Identifying the username that is locking a file for editing

Hi JMan,

I put this part of the code into the "This Workbook"

Private Sub Workbook_Open()
    EndTime = Now + TimeValue("00:01:00")
    [COLOR="Red"]Runtime[/COLOR]
End Sub

and the rest of this code into Sheet1

Private Sub Worksheet_Change(ByVal Target As Range)
    If EndTime Then
        Application.OnTime _
        EarliestTime:=EndTime, _
        Procedure:="CloseWB", _
        Schedule:=False
        EndTime = Empty
    End If
    EndTime = Now + TimeValue("00:01:00")
    RunTime
End Sub
 
 'If no changes have occurred then this runs
[COLOR="red"]Public[/COLOR] EndTime
Sub RunTime()
    Application.OnTime _
    EarliestTime:=EndTime, _
    Procedure:="CloseWB", _
    Schedule:=True
End Sub
 
Sub CloseWB()
    Application.DisplayAlerts = False
    With ThisWorkbook
        .Saved = True
        .Close
    End With
End Sub

When restarted an error comes up on the first part where it states Runtime.

When I remove runtime the error comes up highlighting Public in the second part.

Thanks,

Larry

Re: Identifying the username that is locking a file for editing

The two other subs need to be in a Standard Module, if you only put the worksheet code in sheet1 then changes to other sheets won't register.
I think changing it too 1 minute is too short a delay unless you are only testing the code.

Re: Identifying the username that is locking a file for editing

An alternative solution is to simply have a hidden sheet that stores the username when the file is opened and deletes it when the file is closed. If the file is already open you could display a message box to say that it is already opened by another user.

Insert this code in the Workbook code sheet

Option Explicit


Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Worksheets("Hidden Sheet").Range("A1").Clear
End Sub


Private Sub Workbook_Open()
    If ThisWorkbook.ReadOnly Then
        MsgBox "Already opened by: " & Worksheets("Hidden Sheet").Range("A1")
      Else
        Worksheets("Hidden Sheet").Range("A1") = Environ("Username")
        ThisWorkbook.Save
    End If
End Sub

This little peice of code will display many environment variables, I guess that what you want is either NWUsername or Username. (Code taken from http://www.tek-tips.com/faqs.cfm?fid=4296])

Option Explicit


Private indx As Long


Sub List_Environment_Variables()
Dim EnvString As String
indx = 1
Do
    EnvString = Environ(indx)
    Cells(indx, 1) = EnvString
    indx = indx + 1
Loop Until EnvString = ""
End Sub

A.

Re: Identifying the username that is locking a file for editing

Hi Roy,

I put this part in the "This Workbook"

Private Sub Workbook_Open()
    EndTime = Now + TimeValue("00:01:00")
    RunTime
End Sub

This part into "sheet1"

Private Sub Worksheet_Change(ByVal Target As Range)
    If EndTime Then
        Application.OnTime _
        EarliestTime:=EndTime, _
        Procedure:="CloseWB", _
        Schedule:=False
        EndTime = Empty
    End If
    EndTime = Now + TimeValue("00:01:00")
    RunTime
End Sub

and this part into "Module1"

Public EndTime
Sub RunTime()
    Application.OnTime _
    EarliestTime:=EndTime, _
    Procedure:="CloseWB", _
    Schedule:=True
End Sub


Sub CloseWB()
    Application.DisplayAlerts = False
    With ThisWorkbook
        .Saved = True
        .Close
    End With
End Sub

The sheet disappears after one minute but it does not close the book completely and it's not saving the changed data. Is there something else I'm doing wrong? Itried putting this part below into a separate module but it still does the same thing.

BTW your right the 1 minute is to test the workbook.

Sub CloseWB()
    Application.DisplayAlerts = False
    With ThisWorkbook
        .Saved = True
        .Close
    End With
End Sub

Thanks,

Larry

Re: Identifying the username that is locking a file for editing

I have reset the close time to 10 seconds for testing, of course the user must have macros enabled

Re: Identifying the username that is locking a file for editing

This part of the code is incorrect

Public EndTime 
Sub RunTime() 
    Application.OnTime _ 
    EarliestTime:=EndTime, _ 
    Procedure:="CloseWB", _ 
    Schedule:=True 
End Sub

I'm guessing it should be

Public Sub RunTime() 
    EndTime
    Application.OnTime _ 
    EarliestTime:=EndTime, _ 
    Procedure:="CloseWB", _ 
    Schedule:=True 
End Sub

Re: Identifying the username that is locking a file for editing

then again, I can't get mine to work either so I don't know how that sub is supposed to look

Re: Identifying the username that is locking a file for editing

Hi Roy,

I tried your example, it partially closes the file and does not save the changes. The only thing I did was entered a number into a cell and waited to see if it would save and close. I did enable macros when prompted on open. Is there something else I am supposed to do?

Thanks,

Larry

Re: Identifying the username that is locking a file for editing

Hi,

Back to your origional post have a look at this link http://www.xcelfiles.com/IsFileOpen.html#anchor_37 it tell you who has an open work book, 'though I don't know what happens if more than one person has it open.

Hope this help

FOB

Re: Identifying the username that is locking a file for editing

Just found a couple of examples on Aaron Blood's site that do a shut down

Re: Identifying the username that is locking a file for editing

Hi Roy,

The link you shared was a great help. The two files on it though did not save after closing. so I tried to modify what was there, the code below seems to work pretty good. I can actually make changes to all sheets and it will save all of them after 30 seconds (Test Only). Why did this not require for any code to be in the sheets?

Module1

Dim DownTime As Date


Sub SetTime()
    DownTime = Now + TimeValue("00:45:30")
    Application.OnTime DownTime, "ShutDown"
End Sub


Sub ShutDown()
Application.DisplayAlerts = False
If ActiveWorkbook.ReadOnly = True Then
ActiveWorkbook.Close SaveChanges:=False
Exit Sub
Else
ActiveWorkbook.Close SaveChanges:=True
End If
Application.DisplayAlerts = True
End Sub

This Workbook

Private Sub Workbook_Open()
    Call SetTime
End Sub

Thanks Again,

Larry

Re: Identifying the username that is locking a file for editing

Oops! That one is wrong too. It closes after the alotted time.

I took the original code from Roy and changed

This Part

Sub CloseWB()
    Application.DisplayAlerts = True
    With ThisWorkbook
        .Saved = True
        .Close
    End With
End Sub

To this

Sub CloseWB()
Application.DisplayAlerts = False
If ActiveWorkbook.ReadOnly = True Then
ActiveWorkbook.Close SaveChanges:=False
Exit Sub
Else
ActiveWorkbook.Close SaveChanges:=True
End If
Application.DisplayAlerts = True
End Sub

Thanks,

larry

Re: Identifying the username that is locking a file for editing

I may have missed something here but I thought you wanted to be able to ask the person with the file open to close it and therefore you needed to know who had it open.

Quote from pdsasse

We're looking for a way to determine the username of the person locking it. Not the username as it appears in Excel but the network login name of the user so that we can contact them and ask them to close the file if they are done with it.

The code that appears to be suggested here will either close the workbook after it has been open for a period of time or close it after a period of inactivity.

What if I am in the middle of using the file and keep using every 5 minutes for the next 5 hours? You will either force the close (I think you have rejected that code) or will never get to know who has the file open.

I apologise if I have missed the post that changed the objective,

A.

Re: Identifying the username that is locking a file for editing

I have a couple of associates that like to walk away from their computers without logging off the shared workbook they are in, and it gets sooooooo old to have to chase them down to close the workbook (They are two buildings over). So when Roy displayed the code to close the workbook it peaked my interest. Unfortunately I am not that great at VB sooooooo I had to ask a few more questions, I'm sorry if I offended anybody by loading this thread with information that I thought might be of use to anyone else also. I also appologize to pdsasse the original person that created the thread for changing the objective. I did eventually get the code to work the way I wanted it to, thanks to Roy and JMan.

Larry