VBA Code To Timestamp & Username For Anything That Changes In a Select Range

[tzcoding]

I have both sides of the code but do not know how to join them together. One part of the cod will pull the Windows user name but not work with a selected range and not work with if there is a change in that range. The other part works with with timestamping when ever there is a change within a selected ranch and logs that day.

Timestamp Code:

Public Function ModDate(Reference As Range)
Dim cell As Range

For Each cell In Reference

    ModDate = Format(Now, "dd-mm-yyyy hh:mm AM/PM")

Next cell

End Function

Windows Username:

Public Function UserName()


    UserName = Environ$("UserName")

End Function

Does anyone know how to join the two into one?

 

[Legacy 143009]

This code will log the changes in column Z. This is an event, which means, it will fire up automatically each time a change occures in the sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
  With Application
  .EnableEvents = False
  Cells(Rows.Count, "Z").End(xlUp).Offset(1).Value = .UserName & " has modified cell: " & Target.Address & " at " & Format(Now, "dd-mm-yyyy hh:mm AM/PM")
  .EnableEvents = True
  End With
End Sub

 

[tzcoding]

This code will log the changes in column Z. This is an event, which means, it will fire up automatically each time a change occures in the sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
  With Application
  .EnableEvents = False
  Cells(Rows.Count, "Z").End(xlUp).Offset(1).Value = .UserName & " has modified cell: " & Target.Address & " at " & Format(Now, "dd-mm-yyyy hh:mm AM/PM")
  .EnableEvents = True
  End With
End Sub

This is great i just have a question. I know that it will log the information in column "Z" and i can change it to AE for my target log column. That part is clear to me. The columns i need it to look at are 'A'-'AD' and it has to do it row by row. So if i change anything in columns A'-'AD' row 4 it puts the log in AE row 4.

 

[Legacy 143009]

I think this is what you want. Bare in mind that this will display only the first row index when multiple rows modified.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("A:AD")) Is Nothing Then
    With Application
    .EnableEvents = False
    Cells(Rows.Count, "AE").End(xlUp).Offset(1).Value = .UserName & " has modified column " & Split(Target.Address, "$")(1) & " row " & Target.Cells(1, 1).Row & " at " & Format(Now, "dd-mm-yyyy hh:mm AM/PM")
    .EnableEvents = True
    End With
  End If
End Sub