Workbook_SheetChange - Track Old Value

[Andresuru]

Hello Team,

Appreciate your help with guidance,

I already have a vba code to track date, new value, username etc but I need to track the old value. I tried to track using workbook?selectionchange however is not working and is very important to track the old value.

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

Dim LR As Long
Dim Oval As String

If Sh.Name = "Historial" Then Exit Sub

Application.EnableEvents = False

With Sheets("Historial")

    LR = .Range("A" & Rows.Count).End(xlUp).Row
    .Range("A" & LR + 1).Value = Format(Now, "dd-mm-yy hh:mm:ss")
    .Range("B" & LR + 1).Value = Sh.Name
    .Range("C" & LR + 1).Value = Target.Address(False, False)
    .Range("D" & LR + 1).Value = Target.Value
    .Range("E" & LR + 1).Value = Environ("USERNAME")
    .Range("F" & LR + 1).Value = Oval
    
End With

Application.EnableEvents = True

End Sub

Public Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

Oval = Target.Value

End Sub

Apreciate a lot your help on this.

Regards

 

[jasonb75]

Oval needs to be declared at module level, not procedure level.

Understanding scope and visibility (VBA)

Office VBA reference topic

docs.microsoft.com

 

[Andresuru]

Oval needs to be declared at module level, not procedure level.

Understanding scope and visibility (VBA)

Office VBA reference topic

docs.microsoft.com

Basically I am not sure how to use oval, I just found somewhere in google and try to adapt but i have seing there are many people with same situation and most of the times are getting error.
So if you can help me to see VBA code that can be added to my macro, appreciated.

Regards

 

[jasonb75]

So if you can help me to see VBA code that can be added to my macro, appreciated.

It is in the link that I provided, you have used the first method but you need to use the second one.

Move the line that says Dim Oval As String so that it is above the Private Sub line and it should work fine.

 

[johnnyL]

Hello Team,

Appreciate your help with guidance,

I already have a vba code to track date, new value, username etc but I need to track the old value. I tried to track using workbook?selectionchange however is not working and is very important to track the old value.

Track the old value of what?

 

[Andresuru]

It is in the link that I provided, you have used the first method but you need to use the second one.

Move the line that says Dim Oval As String so that it is above the Private Sub line and it should work fine.

That's resolved the issue, thank you a lot, MOD you can close this thread.

Regards