Auto Insert Date, Time and User Name of Last Modification

[aamartin]

Hello all,

I have a basic understanding of Excel and it's features and have modified very basic VBA code in the past. Okay, so I've built a work schedule encompassing a four week period with various employees and the workbook ("W1 - July 2015 Schedule") is shared amongst various supervisors. I've searched for a VBA code that will automatically insert the Date, Time and User Name of the excel user who last modified any part of the schedule in Cell "T56" in every sheet modified in this format "Revised: 06/30/2015 at 09:52 PM by John Doe" - John Doe being the registered Excel user not the computer user name. I'd like the results to be inserted into the specified cell so it can been seen by all users upon opening the file, rather than having to view the Print Preview. I've found similar code that appeared to do what I need, but haven't been able to make it work in my workbook.

Any help anyone can provide would be greatly appreciated!!

 

[Rtoscano]

Hello V_Malkoti
I was trying to use your formula (copy/paste, only replacing the T56 cell by B1) but I got the message: "Run-time error '2147417848 (80010108)': Method 'Range' of object'_Worksheet' failed "
As 'aamartin', I was trying to introduce in each B1 cell of each worksheet of a given workbook the last modification time and user...
As I'm new to VBA code, I can't sort it out... can you say what I'm doing wrong ? Thank you..

 

[V_Malkoti]

Rtoscano, try this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
[COLOR=#0000ff]    Application.EnableEvents = False[/COLOR]
    Sh.Range("B1") = "Revised: " & Format(Date, "mm/dd/yyyy") & " at " & Format(Time, "hh:mm AMPM") & " by " & Application.UserName
[COLOR=#0000ff]    Application.EnableEvents = True[/COLOR]
End Sub

Need to disable events because setting value in B1 will again trigger the event code, leading to an infinite recursion. After updating the cell, events are enabled again.