Solved: Track Changes On Row Delete

[hobbiton73]

Hi, I wonder whether someone may be able to help me please.

I'm using the code below to track changes and insert pre-defined text value upon a cell change within a given range.

[vba]
Option Explicit
Public preValue As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rInt As Range, res As Variant
Dim cell As Range
Sheets("Input").Protect "password", UserInterFaceOnly:=True
Set rInt = Intersect(Target, Range("B7:Q350"))

If Not rInt Is Nothing Then
For Each cell In rInt
If Not IsEmpty(cell.Value2) Then
cell.Interior.ColorIndex = 35

Application.EnableEvents = False
With Rows(cell.Row)
.Range("A1").Value = Date
.Range("G1").Value = "R&D"
.Range("AE1").Value = "No"
End With

Application.EnableEvents = True
End If
Next cell
End If
End Sub[/vba]

This piece of the code works fine in it's own right. But I have an issue when the user deletes a row.

Unfortunately, when the row is deleted, the 'Track change' code is then activated, when in reality there has been no cell value change.

Because I have 'locked' cells within a 'Protected' worksheet I'm using the following code to allow the user to delete the row:

[vba]
Sub DelRow()

Dim msg

msg = MsgBox("Are you sure?", vbYesNo)
If msg = vbNo Then Exit Sub

ActiveSheet.Unprotect "handsoff"
Selection.EntireRow.Delete
ActiveSheet.Protect "handsoff"

End Sub
[/vba]

I must admit, I'm not really sure how to overcome this. I just wondered whether someone perhaps offer some guidance please on how I can stop the 'Track Change' activating when a row is deleted.

Any help would be very much appreciated.

Many thanks and klind regards

[Jan Karel Pieterse]

I'd turn EnableEvents off just before the deleting and then "manually" log the deletion from the deleting code. Then turn events back on.

[hobbiton73]

Hi @Jan Karel Pieterse, thank you very much for taking the time to reply to my post.

Your solution worked great.

Once again many thanks and kind regards

[ASJ]

Hi Jan,

Thank you for the suggestion, For track changes in excel I am referring to code from different forum and have made necessary modifications where track changes code is working fine, except while deleting or adding row.
Could you please help (I am new to vba macros) with the code, where if the row is deleted or added then do nothing or else run through the remaining code.

Below is the code

Private Sub Worksheet_Change(ByVal Target As Range)\For Each j In Target
Dim wb As Workbook
Dim ws2 As Worksheet
Dim NextRow As Long
Set wb = ThisWorkbook
Set ws2 = wb.Worksheets("Change Log")    'Sheet name where i am tracking the changes 
NextRow = ws2.Cells(Rows.Count, 1).End(xlUp).Row + 1
Dim CellAdd As String
CellAdd = j.Address
Dim sht As String
sht = ActiveSheet.Name
Dim newValue As String
newValue = j.Value
Application.EnableEvents = False
Application.Undo
oldValue = j.Value
Application.Undo
Application.EnableEvents = True
ws2.Cells(NextRow, 1) = sht & " " & "-" & " " & CellAdd & " was changed to '" & newValue & "' from '" & oldValue & "' by " & Environ("username") & " " & "on" & " " & Format(Now(), "M-DD-YYYY H:MM:ss")
Next j
End Sub

Thanks and Regards,
ASJ.