Help me get Excel activity log to record previous value & deleted text

[BonnieM]

Hi Team,

After much searching, I found this code to create a log of user changes to an excel spreadsheet. It records when text is entered or changed, but not if deleted.

Also, it does not show the previous value - always shows as blank - it seems the error handling is resetting PreviousValue to 0

I tried going to author of code, but he hasn'et answered - can anyone here assist in recording PreviousValue AND showing when text is deleted?


Sample from current log when text is added to blank cell or new text is added:

9/2/2015 11:35:08 AMMeck, Bonnie changed cell $H$3 from to test

Private Sub Worksheet_Change(ByVal Target As Range)
    
    'define variables
        Dim sLogFileName As String, nFileNum As Long, sLogMessage As String
        Dim PreviousValue
    
    'only do these things if NOT read-only
        If ThisWorkbook.ReadOnly = False Then
    
    'do not log inserting of rows or columns (as this would only be done by admin after unprotecting sheet)
        If Target.Rows.Count > 1 Then Exit Sub
        If Target.Columns.Count > 1 Then Exit Sub
    
    'define path for log file
        sLogFileName = "S:\Data Quality\SOX\ICRR Documentation\tracker test versions\Tracker Log.txt"
    
    ' Turn on error handling
        On Error Resume Next
        If Target.Value <> PreviousValue Then
    'Check if we have an error
            If Err.Number = 13 Then
            PreviousValue = 0
            End If
    'Turn off error handling
        On Error GoTo 0
        
    'define log data
        sLogMessage = Now & Application.UserName & " changed cell " & Target.Address _
        & " from " & PreviousValue & " to " & Target.Value


    'next file number
        nFileNum = FreeFile
     
    'create the file if it doesn't exist
        Open sLogFileName For Append As #nFileNum
        
    'append information
        Print #nFileNum, sLogMessage
        
    'close the file
        Close #nFileNum
    
    'if activity, reset the timer
        Monitor OnTimeAction:=xlOnTimeUpdate
    
        End If
    
Else
 
End If
        
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim PreviousValue
    PreviousValue = Target(1).Value


'if activity, reset the timer
    Monitor OnTimeAction:=xlOnTimeUpdate


End Sub

 

[BonnieM]

Solved