Issue with code for tracking work

[nparsons75]

Hi, I am using this code to track work changes on a sheet. Everything works perfectly apart from the tracking of the old value. I see what the value has changed too, but It will not show what the value has changed from (old value).

Option Explicit

Dim vOldVal 'Must be at top of module
Private Sub Worksheet_Change(ByVal Target As Range)
Dim bBold As Boolean




If Target.Cells.Count > 1 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 Sheet2
        .Unprotect Password:="OEESTAT"
        If .Range("A9") = vbNullString Then
            .Range("A9:E9") = Array("CELL CHANGED", _
                    "OLD VALUE", _
            "NEW VALUE", "TIME OF CHANGE", "DATE OF Change ")
        End If




        With .Cells(.Rows.Count, 1).End(xlUp)(2, 1)
        .Value = Target.Address
        .Offset(0, 1) = vOldVal
            With .Offset(0, 2)
              If bBold = True Then
            .ClearComments
            .AddComment.Text Text:= _
                "LUR Forecast:" & 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:="OEESTAT"
    End With
    vOldVal = vbNullString




    With Application
    .ScreenUpdating = True
    .EnableEvents = True
    End With




On Error GoTo 0
End Sub


'Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 'vOldVal = Target
'End Sub


Hope someone can help.  Thanks

 

[Johnny C]

There are various ways you can do it. Since the VBA is only triggered after the value has changed you have to be a bit sneaky, but it's simple enough.

http://access-excel.tips/value-before-worksheet-change/