Problem with Audit Trail not logging old values

[crookesa]

I found the following code posted by @Logit and have been using it and it's working well.

I've just tried to use it in another workbook which has user input validated via a drop down list using data validation.

One thing I've noticed is that the old value isn't always recorded in the log when values are change via the drop down or using the delete key.

Is this something that can be fixed or someone has seen before? I've tried to trace the vOldVal varaible but can't seem to spot why it's empty at times.

Any help would be great.

Option Explicit

Dim vOldVal 'Must be at top of module

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim bBold As Boolean

If Target.CountLarge > 1 Then Exit Sub
If ActiveSheet.Name = "AUDIT" Then Exit Sub

'On Error Resume Next

    With Application
         .ScreenUpdating = False
         .EnableEvents = False
    End With

    If IsEmpty(vOldVal) Then vOldVal = "Empty Cell"
    bBold = Target.HasFormula
        With Sheets("AUDIT")
            '.Unprotect Password:="Secret"
                If .Range("A1") = vbNullString Then
                    .Range("A1:F1") = Array("Cell Changed", "Old Value", _
                        "New Value", "TIME", "DATE", "USER")
                End If

            With .Cells(.Rows.Count, 1).End(xlUp)(2, 1)
                  .Value = ActiveSheet.Name & "!" & Target.Address
                  .Offset(0, 1) = vOldVal
            With .Offset(0, 2)
              If bBold = True Then
                .ClearComments
                .AddComment.Text Text:= _
                     "NOTE :" & Chr(10) & "" & Chr(10) & _
                        "Bold values are the results of formulas"

              End If
                .Value = Target
                .Font.Bold = bBold

            End With
                .Offset(0, 3) = Time
                .Offset(0, 4) = Date
                .Offset(0, 5) = Application.UserName
            End With
            .Cells.Columns.AutoFit
            '.Protect Password:="Secret"
        End With

    vOldVal = vbNullString

    With Application
         .ScreenUpdating = True
         .EnableEvents = True
    End With
    
On Error GoTo 0

'MsgBox "There was a change to this sheet!"

End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    vOldVal = Target
End Sub

 

[crookesa]

Just a bump to try see if anyone knows why this is happening?

Seems to be when the cell value is populated or removed from a cell that has data validation enabled but I can't work out why it's capturing it.