Identify When, What & Who made changes to a cell.

[Anthony G.]

Hello to all!!

I came across this handy little code to identify cell changes and it works rather well. However, if the report is being sent out to various people, can the code be adjusted to identify the "WHO" or can that only be done if the workbook is shared?

Here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.ColorIndex = 8
On Error Resume Next
Dim curComment As String
curComment = ""
curComment = Target.Comment.Text
If curComment <> "" Then curComment = curComment & Chr(10)
Target.AddComment
Target.Comment.Text Text:=curComment & _
ActiveWorkbook.BuiltinDocumentProperties("Author") & _
Chr(10) & " Rev. " & Format(Date, "yyyy-mm-dd ") & _
Format(Time, "hh:mm")
'comment perhaps should be resized
End Sub

Thanks,

Anthony

 

[dk]

Hi,

Just add Application.UserName to the bottom of your code e.g.

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.ColorIndex = 8
On Error Resume Next
Dim curComment As String
curComment = ""
curComment = Target.Comment.Text
If curComment <> "" Then curComment = curComment & Chr(10)
Target.AddComment
Target.Comment.Text Text:=curComment & _
ActiveWorkbook.BuiltinDocumentProperties("Author") & _
Chr(10) & " Rev. " & Format(Date, "yyyy-mm-dd ") & _
Format(Time, "hh:mm") & ".  " & Application.UserName
'comment perhaps should be resized
End Sub

 

[Anthony G.]

Thanks DK....worked great. One more question, if you don't mind. Would you happen to know how to force a macro upon a user opening the workbook?

Thanks Again!!!

Anthony