0

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
BigBen
  • 46,229
  • 7
  • 24
  • 40
Kano
  • 37
  • 7
  • 1
    I don't think this approach is going to work. Only one user at a time can have an Excel file open with write permission. This macro requires write permission. Therefore... – Nicholas Hunter Apr 06 '21 at 13:53
  • Yes from what I have been searching since, you are correct. I was thinking if someone has an idea on how to copy it to a text file for example, if that's possible. Or using any other tool if that's easier. – Kano Apr 06 '21 at 14:32
  • Maybe this will help [How can I write to a text file reliably from Excel VBA?](https://stackoverflow.com/questions/3085615/how-can-i-write-to-a-text-file-reliably-from-excel-vba) – Nicholas Hunter Apr 06 '21 at 15:10

0 Answers0