Track changes insert column/row

[pr1asd]

Hi All,

I am trying to get excel to track and record all changes made to a workbook. However, this does not work when a row or column is inserted.

Instead it shows Run Time Error '13': Type mismatch error.

I know that this is because the code is not set-up for tracking this kind of change and I was wondering if anyone can help me change it so that it will record this change.

Here is the code so far:

[VBA]
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim NewVal As String
Dim oldVal As String
Dim lr As Long

If ActiveSheet.Name = "Changes" Then Exit Sub

Application.EnableEvents = False

NewVal = Target.Value

Application.Undo

oldVal = Target.Value

lr = Sheets("Changes").Range("A" & Rows.Count).End(xlUp).Row + 1

Sheets("Changes").Range("A" & lr) = Now
Sheets("Changes").Range("B" & lr) = ActiveSheet.Name
Sheets("Changes").Range("C" & lr) = Target.Address
Sheets("Changes").Range("D" & lr) = oldVal
Sheets("Changes").Range("E" & lr) = NewVal
Sheets("Changes").Range("F" & lr) = UNameWindows()

Target = NewVal

Application.EnableEvents = True
End Sub
[/VBA]

I know what I would like for the end of the code:

[VBA]
lr = Sheets("Changes").Range("A" & Rows.Count).End(xlUp).Row + 1

Sheets("Changes").Range("A" & lr) = Now
Sheets("Changes").Range("B" & lr) = ActiveSheet.Name
Sheets("Changes").Range("C" & lr) = Target.Address
Sheets("Changes").Range("D" & lr) = "Row/Column inserted before "&

Sheets("Changes").Range("F" & lr) = UNameWindows()

[/VBA]

But I am not sure how to get it to do this for an insertion or how to get it to record the row/column number/letter.

Please can someone help, this is driving me crazy?

Thanks

PR1ASD

P.S. the code used was from this website: forums.techguy.org/business-applications/810202-solved-track-changes-using-ms.html

[mikerickson]

Instead of tracking Target.Value, try tracking both Target.Address(,,,True) and Target.Cells(1,1).Value

BTW, it also look like the current code will also error if you copy more than one cell and pasted it into the worksheet.