Macro to keep track of username and time for users who opens an excel file

[Jadvanianil]

Dear Team,
I have got a macro in an excel file which tracks the username and time of the users opening the file which I definitely need. The problem is that if 1 user opens the file and leaves it open then it doesn't track the name/time of other users who open the file when its in use. Is it anyway possible to track every user who opens the file even if the file is in use? I have copied the original code below. Many Thanks.

Option Explicit

Private LastRow As Long
Const HIDDEN_SHEET As String = "hidden"

Private Sub Workbook_Open()

Application.EnableEvents = False
With Worksheets(HIDDEN_SHEET)
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
.Range("A" & LastRow).Value = Environ("UserName")
.Range("B" & LastRow).Value = Format(Date + Time, "dd mmm yyyy hh:mm:ss")
End With
ActiveWorkbook.Save
Application.EnableEvents = True
End Sub

 

[Zot]

See if the solution here can be used

Track opening time and closing time ofworkbook

Please I may want a code to calculate the time I used in working with a workbook. The details are when I open the workbook it stores it in a cell then when closing it stores it in another cell then calculate the difference between the my opening time and closing time and present it to me with a...

www.mrexcel.com

 

[Jadvanianil]

Hi Zot,
Thank you very much for pointing towards this link. Its definitely very interesting and useful for the purpose. I tried using some code from it but again it's the same issue I am facing when more than 1 person opens a file, it only captures the details of the 1st person who opens the file and not for anyone else whilst the file is in use/open.

 

[Zot]

Hi Zot,
Thank you very much for pointing towards this link. Its definitely very interesting and useful for the purpose. I tried using some code from it but again it's the same issue I am facing when more than 1 person opens a file, it only captures the details of the 1st person who opens the file and not for anyone else whilst the file is in use/open.

My fault. I did not read the problem you're facing properly.

 

[Zot]

I have no experience with shared workbook but this could guide you to help solving your problem perhaps?

Show List Of Active Users In Shared Workbook

Hi all, Is there a code that would display the names of all the people that have a shared workbook open, within the workbook? We have a document at work that is shared (and each of our names are tied to our log on, so when I go to Shared it shows me the names of who has it open) and I would...

chandoo.org

 

[Jadvanianil]

Hi Zot, i did look into this but still struggling to solve the issue. I have seen some links where they use other applications to interact with excel, but that gets too complicated so just hoping to find the solution via vba. thanks.

 

[Zot]

Hi Zot, i did look into this but still struggling to solve the issue. I have seen some links where they use other applications to interact with excel, but that gets too complicated so just hoping to find the solution via vba. thanks.

I'm bookmarking this and will look for method when I'm free

 

[Jadvanianil]

Thank you very much, and I will post it if I find some solution.