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
Adam wrote:
>
> 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
|
Similar Threads
