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

Jadvanianil

New Member
Joined
Feb 21, 2014
Messages
19
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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,170
Office Version
  1. 2016
Platform
  1. Windows
See if the solution here can be used

 

Jadvanianil

New Member
Joined
Feb 21, 2014
Messages
19
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

Well-known Member
Joined
Nov 26, 2020
Messages
1,170
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Nov 26, 2020
Messages
1,170
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

 

Jadvanianil

New Member
Joined
Feb 21, 2014
Messages
19
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

Well-known Member
Joined
Nov 26, 2020
Messages
1,170
Office Version
  1. 2016
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,804
Messages
5,638,455
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top