Find out who last modified excel file...

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?
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Hi

If it's stored on a network I'd have a word with your IT support. They may be able to identify the person who last modified the file and then through a process of restoring back up's you could build up a history. Bit of pain but can't think of anything else I'm afraid.

Dom
 
Upvote 0
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
 
Upvote 0
Hi:
I just tried this on excel 2010 and it did not work.
i am sure i am missing something, please help me.
I opend a new excel worksheet, added a new sheet and called it Log
open VB in excel and double clicked "this work
book" copied and pasted the code, saved everything and closed excel.
open excel again and types something in sheet1
saved and closed
open excel again and there was nothing in the Log sheet!
what am i missing?

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
 
Upvote 0
How do you show the results in the Log Sheet? I am assuming you would need to enter an function.

Thank you


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
 
Upvote 0
Zombie post, but the code did work for me. And the results are listed in the log sheet in the order the changes were made.
 
Upvote 0
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
 
Upvote 0
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

I have put this code into my spreadhseet but I am getting a run time error 6 regarding the "If Target.Cells.Count > 1 Then Exit Sub" part. How can I fix this?
 
Upvote 0
Try using Countlarge rather than Count
 
Upvote 0

Forum statistics

Threads
1,223,249
Messages
6,171,031
Members
452,374
Latest member
keccles

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
Back
Top