ASJ
12-11-2023, 12:27 PM
Hello everyone,
I have a vba code which works fine while updating cell value in excel, 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
I have a vba code which works fine while updating cell value in excel, 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