eesany
04-11-2016, 03:39 AM
Dear Kind Souls,
I have a problem that I need help on. I created a log in a file to track changes made to column C. This works fine when the user only updates cells in C. However, my users sometimes insert and delete rows, and these changes are also logged.
Can anyone advise how should I modify my macro to exclude changes due to rows insertion and deletion?
Here are my macros:
Dim CheckChange
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lngLastRow As Long
On Error Resume Next
If Target.Column = 3 And Target.Value <> CheckChange Then
lngLastRow = Sheet10.Range("A" & Rows.Count).End(xlUp).Row
Sheet10.Range("A" & lngLastRow + 1) = Format(Date, "mm-dd-yyyy")
Sheet10.Range("B" & lngLastRow + 1) = Format(Now, "h:mm:ss")
Sheet10.Range("C" & lngLastRow + 1) = Environ("UserName")
Sheet10.Range("D" & lngLastRow + 1) = CheckChange
Sheet10.Range("E" & lngLastRow + 1) = Target.Value
Sheet10.Range("F" & lngLastRow + 1) = ActiveSheet.Name
Sheet10.Range("G" & lngLastRow + 1) = Target.Address
Sheet10.Range("H" & lngLastRow + 1) = ActiveCell.Offset(-1).Offset(, -2)
Sheet10.Range("I" & lngLastRow + 1) = ActiveCell.Offset(-1).Offset(, 2)
Sheet10.Range("J" & lngLastRow + 1) = ActiveCell.Offset(-1).Offset(, 3)
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
CheckChange = Target.Value
End Sub
I have a problem that I need help on. I created a log in a file to track changes made to column C. This works fine when the user only updates cells in C. However, my users sometimes insert and delete rows, and these changes are also logged.
Can anyone advise how should I modify my macro to exclude changes due to rows insertion and deletion?
Here are my macros:
Dim CheckChange
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lngLastRow As Long
On Error Resume Next
If Target.Column = 3 And Target.Value <> CheckChange Then
lngLastRow = Sheet10.Range("A" & Rows.Count).End(xlUp).Row
Sheet10.Range("A" & lngLastRow + 1) = Format(Date, "mm-dd-yyyy")
Sheet10.Range("B" & lngLastRow + 1) = Format(Now, "h:mm:ss")
Sheet10.Range("C" & lngLastRow + 1) = Environ("UserName")
Sheet10.Range("D" & lngLastRow + 1) = CheckChange
Sheet10.Range("E" & lngLastRow + 1) = Target.Value
Sheet10.Range("F" & lngLastRow + 1) = ActiveSheet.Name
Sheet10.Range("G" & lngLastRow + 1) = Target.Address
Sheet10.Range("H" & lngLastRow + 1) = ActiveCell.Offset(-1).Offset(, -2)
Sheet10.Range("I" & lngLastRow + 1) = ActiveCell.Offset(-1).Offset(, 2)
Sheet10.Range("J" & lngLastRow + 1) = ActiveCell.Offset(-1).Offset(, 3)
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
CheckChange = Target.Value
End Sub