Track who opened Excel Sheet - Access Logs

It very likely that multiple people would be working on the same excel sheet for updating the data. For example you want to gather some information from your employees like their address, then you can create an excel sheet and save it on a shared location. Your employees can open the file from that location and fill their details.

There might be a need where you might also be interested in knowing who has saved the excel sheet. This way you can track who has not filled their details!!

You can create a Macro in excel to enable user logs. Whenever a person saves any data in the sheet, his/her system username and time of saving can be stored. Follow the below steps.

1. Create a Sheet named "Log" with the following format.

Timestamp	Username

2. Copy the following code in the Workbook_Open Event

Private Sub Workbook_Open()
Worksheets("Log").Range("A65536").End(xlUp).Offset(1, 0).Value _
    = Format(Now(), "mm-dd-yy HH MM AMPM")
Worksheets("Log").Range("B65536").End(xlUp).Offset(1, 0).Value _
    = Environ("UserName")
End Sub

3. Next whenever anyone opens the Excel Sheet his name will be stored in the Sheet "Log". You can hide this sheet so that others cannot see and modify it.

Simple and neat trick!!

Do you want to log the

Do you want to log the viewing of the individual worksheets or log only if they do any changes?

Individual sheets

Hi Ravi,
Is it possible to log individual sheets within the workbook.

The above works excellently for the workbook itself and appends every time users open it.
I have a workbook which contains 20 sheets, each are named individually. Is it possible to log the user accessing these individual sheets rather than the workbook and in addition to logging the date, time and user as with the above code, log the name of the sheet viewed?
Is it also possible to store the log on an entirely different workbook?

Cheers

Jeff

where this LOG file need to

where this LOG file need to save . . .

Post new comment

The content of this field is kept private and will not be shown publicly.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.

More information about formatting options

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
14 + 0 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.

You may also like to read

Suggested Book to learn VBA