Application.undo runtime error '1004'

[Adam]

Hi,

I am using a vba code to track changes made to a spreadsheet on another
sheet.

Code:
=====================================================
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If ActiveSheet.Name = "Changelog" Then Exit Sub
Application.EnableEvents = False

UserName = Environ("USERNAME")

Sheets("Changelog").Unprotect ("test")
NewVal = Target.Value
Application.Undo
oldVal = Target.Value

lr = Sheets("Changelog").Range("A" & Rows.Count).End(xlUp).Row + 1
Sheets("Changelog").Range("A" & lr) = Now
Sheets("Changelog").Range("B" & lr) = ActiveSheet.Name
Sheets("Changelog").Range("C" & lr) = Target.Address
Sheets("Changelog").Range("D" & lr) = oldVal
Sheets("Changelog").Range("E" & lr) = NewVal
Sheets("Changelog").Range("F" & lr) = UserName
Target = NewVal
Application.EnableEvents = True

Sheets("Changelog").Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True, Password:="test"


End Sub

===================================================


My problem is I can't figure out how to code to where the user is able to
add/delete rows. Currently when someone tries to add or delete a row they
will receive a Run Time error '1004'.

I tried an If Activesheet.EntireRow.Insert then statement but that failed
miserably.
If I can track additions and deletions of rows, great, but if all changes
are tracked except for the adding/deleting of rows, that would suffice.

Thanks in advance.

 

[Dave Peterson]

First, I don't see a .unprotect line before you try to change anything.

If you want to test to see if you're working with an entire row or column...

if target.address = target.entirerow.address then
'entire row


if target.address = target.entirecolumn.address then
'entire column

========
And if you're processing multiple cells at a time, then you're going to have to
loop through the changed cells. And probably keep track of each of the values
in each of the areas that changed.

It may be simpler to just ignore(!) those changes.

if target.cells.count > 1 then exit sub

====
And instead of using the Activesheet, use the object that VBA shares with you:
Sh

If lcase(Sh.Name) = lcase("Changelog") Then Exit Sub
and
Sheets("Changelog").Range("B" & lr) = Sh.Name