Last Modified Date, Last Modified By, Last Modified Cell - of a cell in excel using VBA

[divtjd]

Hi All,

I have an excel sheet with data as given below
Column A: ID
Column B: Last Modified Date
Column C: Last Modified By
Column D: Last Modified Cell
Column E to Column Z: columns with actual business data

Requirement: If I make changes to value in any cell in a row from Column E through Column Z, I want to be able to print the Last Modified Date, Last Modified By (username), Last Modified Cell (with column alphabet and Row number) in columns B, C, D respectively along that row.
I got the code from another thread for getting the Last Modified Date and Last Modified By. Given code below. I pasted this code in the "View Code" window of that excel sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("E2:Z9999")) Is Nothing Then Exit Sub
Cells(Target.Row, "B") = Now()
Cells(Target.Row, "C") = Application.Username
End Sub

This piece of code worked and the columns B and C were populated with the Last Modified Date and Username, when I change the values in any cells from E2 to Z9999

Need Help 1: I do not know how to print the cell details i.e. Last Modified Cell, in column D. For example: If I am changing a value in say cell G12, I want to display the column alphabet and row number details like "G12" in the column D i.e. Last Modified cell. Please help by giving the piece of code for getting this output.

Need Help 2: I am not able to Undo (i.e. do a Ctrl+Z) if I make a change in Column E to Column Z using the macro above. Is there a way to allow Undo? Please help with the code or suggestion regarding Ctrl+z not working

 

[offthelip]

You have got two questions, this might answer the first one.., you say "print the cells details" if you mean save the modified cell address in column D then this should do what you want:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("E2:Z9999")) Is Nothing Then Exit Sub
Cells(Target.Row, "B") = Now()
Cells(Target.Row, "C") = Application.UserName
Cells(Target.Row, "D") = Target.Address
End Sub

 

[divtjd]

You have got two questions, this might answer the first one.., you say "print the cells details" if you mean save the modified cell address in column D then this should do what you want:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("E2:Z9999")) Is Nothing Then Exit Sub
Cells(Target.Row, "B") = Now()
Cells(Target.Row, "C") = Application.UserName
Cells(Target.Row, "D") = Target.Address
End Sub

Thank you so much! This helps. Yes by print I actually meant save.

 

[divtjd]

Hi offthelip / All,

Need one more help please with the macro. In my excel sheet, I've made columns B, C, D as Protected/Locked i.e. a user should not be able to change the values in these columns. A user can only edit the values from E2:Z9999. However, the macro is not working - when I change the values in E2:Z9999, the last modified date, last modified by and last modified cell in columns B,C,D is not getting populated with the values and a Runtime error 1004 is thrown.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("E2:Z9999")) Is Nothing Then Exit Sub
Cells(Target.Row, "B") = Now()
Cells(Target.Row, "C") = Application.UserName
Cells(Target.Row, "D") = Target.Address(RowAbsolute:=False)
End Sub

Please help with the code for unprotecting the columns B,C,D when the macro executes and protecting back the columns B,C,D once the macro finishes its execution.

Thanks!
- TJD

 

[offthelip]

try this:~

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("E2:Z9999")) Is Nothing Then Exit Sub
ActiveSheet.Unprotect
Cells(Target.Row, "B") = Now()
Cells(Target.Row, "C") = Application.UserName
Cells(Target.Row, "D") = Target.Address(RowAbsolute:=False)
ActiveSheet.Protect
End Sub