VBA Code to Track Changes in Worksheet

[RJAY8989]

Hello Everyone,

I'm trying to incorporate a code that will track all changes made to a worksheet. The code below works great, however, I can't get Target.Address to update when I insert a row.
For example, say if I put "test" in A4, and I insert a row, and "test" is now in A5...I can't get the Target.Address value in 'Log Sheet' to update accordingly


Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rw As Long
Dim strAddress As String
Dim strUserName As String
Dim dtmTime As Date
Dim val As Variant

If Intersect(Target, Range("A:M")) Is Nothing Then Exit Sub

dtmTime = Now()
val = Target.Value
strAddress = Target.Address
strUserName = Environ("UserName")

Rw = Sheets("Log Sheet").Range("A" & Rows.Count).End(xlUp).Row + 1
With Sheets("Log Sheet")
.Cells(Rw, 1) = strUserName
.Cells(Rw, 2) = strAddress
.Cells(Rw, 3) = val
.Cells(Rw, 4) = dtmTime
End With
End Sub

 

[dangelor]

If you are inserting an entire row at Row 4, your Target will be the active cell of that selection, A4, which is blank.

 

[My Aswer Is This]

I see what your wanting but I would have no ideal how this could be added into the script.
The script is recording what was done and when. Adding a row or deleting rows or columns and wanting the script to then go back and adjust things I would think would be difficult.

Lets say the user deletes rows then adds rows and deletes columns and on and on through out the day and wanting the script to make adjustments I would think is difficult.

I will keep monitoring this thread to see if someone else has an answer.

 

[JoeMo]

When you insert an entire row, the target (for worksheet_change) will be the entire row. So, add to your code a line to insert a full row at the same row in your log sheet. Example if Target.Address = Target.Row.Address then you have inserted an entire row. Now insert a row in log sheet like this:

Sheets("Log Sheet").Row(Target.Row).Insert

 

[RJAY8989]

Sorry, just wanted to bump this to see if there any other suggestions. Could I possibly use the Address / Lookup function? (apologies...I'm fairly new to Excel)

Thanks!

 

[JoeMo]

Sorry, just wanted to bump this to see if there any other suggestions. Could I possibly use the Address / Lookup function? (apologies...I'm fairly new to Excel)

Thanks!

Did you try the suggestion in post #4?

 

[RJAY8989]

Did you try the suggestion in post #4?

Thanks for the reply Joe.

I inserted your suggestion "Sheets("Log Sheet").Row(Target.Row).Insert" into my code in Worksheet_Change but I get an error.
Sorry I'm fairly new to VBA. Could you please tell me what my entire code will be? Thanks!

 

[JoeMo]

Thanks for the reply Joe.

I inserted your suggestion "Sheets("Log Sheet").Row(Target.Row).Insert" into my code in Worksheet_Change but I get an error.
Sorry I'm fairly new to VBA. Could you please tell me what my entire code will be? Thanks!

Untested so may need some tweaking.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rw As Long
Dim strAddress As String
Dim strUserName As String
Dim dtmTime As Date
Dim v As Variant

If Intersect(Target, Range("A:M")) Is Nothing Then Exit Sub
If Target.Address = Target.EntireRow.Address Then
    Sheets("Log Sheet").Rows(Target.Row).Insert
    If Application.CountA(Target) = 0 Then Exit Sub
End If
dtmTime = Now()
v = Target.Value
strAddress = Target.Address
strUserName = Environ("UserName")

Rw = Sheets("Log Sheet").Range("A" & Rows.Count).End(xlUp).Row + 1
With Sheets("Log Sheet")
    .Cells(Rw, 1) = strUserName
    .Cells(Rw, 2) = strAddress
    .Cells(Rw, 3) = v
    .Cells(Rw, 4) = dtmTime
End With
End Sub