tylerewald
New Member
- Joined
- Aug 4, 2007
- Messages
- 1
I am needing to find out who last modified (preferbly the last 5) a excel spreadsheet, does anyone know how to go about doing this?
Option Explicit
Dim Previous As String
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Previous = Target.Formula
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = "Log" Then Exit Sub
Application.EnableEvents = False
On Error Resume Next
With Sheets("Log").Cells(Rows.Count, 1).End(xlUp)
.Offset(1, 0).Value = Environ("UserName")
.Offset(1, 1) = Sh.Name
.Offset(1, 2) = Target.Address
.Offset(1, 3) = "'" & Target.Formula
.Offset(1, 4) = Previous
Previous = ""
.Offset(1, 5) = Now
End With
Application.EnableEvents = True
End Sub
Hello tylerewald
Remember that to modify a file one needs to save it. So this is NOT to protect your privacy but rather to tell who modified the file.If you want to know who last OPENED your file then there's a different code for doing that.
This code I've picked up from somewhere will :
Tell you who modified the file the date and time of the modification,what cell was modified, from what value to what value and on what sheet the modification took place. (talk about details here).
You will need to make the first modification yourself then hide the sheet called "Log"
To know who did what you will unhide this sheet.
Periodically delete most of it and hide it again otherwise the list will get longer and longer with time.
So now do this:
In the Vb editor open the "This workbook" screen and paste the following code.
Code:Option Explicit Dim Previous As String Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub Previous = Target.Formula End Sub Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Sh.Name = "Log" Then Exit Sub Application.EnableEvents = False On Error Resume Next With Sheets("Log").Cells(Rows.Count, 1).End(xlUp) .Offset(1, 0).Value = Environ("UserName") .Offset(1, 1) = Sh.Name .Offset(1, 2) = Target.Address .Offset(1, 3) = "'" & Target.Formula .Offset(1, 4) = Previous Previous = "" .Offset(1, 5) = Now End With Application.EnableEvents = True End Sub
Hello tylerewald
Remember that to modify a file one needs to save it. So this is NOT to protect your privacy but rather to tell who modified the file.If you want to know who last OPENED your file then there's a different code for doing that.
This code I've picked up from somewhere will :
Tell you who modified the file the date and time of the modification,what cell was modified, from what value to what value and on what sheet the modification took place. (talk about details here).
You will need to make the first modification yourself then hide the sheet called "Log"
To know who did what you will unhide this sheet.
Periodically delete most of it and hide it again otherwise the list will get longer and longer with time.
So now do this:
In the Vb editor open the "This workbook" screen and paste the following code.
Code:Option Explicit Dim Previous As String Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub Previous = Target.Formula End Sub Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Sh.Name = "Log" Then Exit Sub Application.EnableEvents = False On Error Resume Next With Sheets("Log").Cells(Rows.Count, 1).End(xlUp) .Offset(1, 0).Value = Environ("UserName") .Offset(1, 1) = Sh.Name .Offset(1, 2) = Target.Address .Offset(1, 3) = "'" & Target.Formula .Offset(1, 4) = Previous Previous = "" .Offset(1, 5) = Now End With Application.EnableEvents = True End Sub
They this if this code isn't working for you.
Open the VB editor with alt+F11
Then in the "Immediate" box at the bottom type
Code:Application.EnableEvents = True
This worked for me