Hi I have a change log sup that faithfully records every change but I can't get it to save the old value. I have tried setting a variable in selection change event but nothing happens whe the change event calls the sub apart form completeing all the other values.
The original value cell (column D) remains blank
Private Sub Worksheet_Change(ByVal Target As Range)
Call UpdateLog(Target)
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
oval = Target.Value
End Sub
Dim oval As String
Sub UpdateLog(c As Range)
Dim LR As Long
If c.Row > 1 Then 'stop recording filter changes
Application.DisplayAlerts = False
Application.ScreenUpdating = False
CurrentSheet = ActiveSheet.Name
CurrentCell = c.Address(False, False)
Change = c.Value
If CurrentSheet <> "Test" Or CurrentSheet <> "Log" Then 'exclude sheets that dont need logging
With Sheets("Log")
LR = .Range("A" & Rows.Count).End(xlUp).Row + 1
.Unprotect Password:="ALP"
.Range("B" & LR).Value = CurrentSheet
.Range("C" & LR).Value = CurrentCell
.Range("D" & LR).Value = oval
.Range("E" & LR).Value = Change
.Range("F" & LR).Value = Environ("UserName")
.Range("G" & LR).Value = Now()
.Range("A" & LR).Value = ThisWorkbook.Name
.Protect Password:="ALP"
End With
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Else
Exit Sub
End If
End If
End Sub
Bookmarks