Using VBA how do I detect when any value in a worksheet changes?

Question

I have a worksheet that contains a few columns with hundreds of values. I want cell A1 to say "Value Changed" as soon as any value changes in the worksheet. I tried to make some code like what you see below, but I couldn't think of how to capture the original value in the OriginalValue variable. How can I detect when the value in any cell changes from something that is different than the Target value?

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Value <> OriginalValue Then
    Range("A1").Value = "Value Change"
End If

End Sub

In `_Selection` change event store the value of the cell in a `variable`. In `_Change` event, simply compare the `target.value` with that `variable`. Also since you are writing to a cell in `_Change` event, you may want to switch off events to prevent possible endless loop. You may want to see [This](http://stackoverflow.com/questions/13860894/ms-excel-crashes-when-vba-code-runs/13861640#13861640) — Siddharth Rout, May 28 '15 at 17:52
In my above comment, I am assuming that you want to trap only a single cell value. If there are multiple cells then you will have to use an array variable. — Siddharth Rout, May 28 '15 at 17:53
Thinking about this from the opposite angle, what will clear the value of `Cell("A1")`? The first changed cell will set it, but at what point will it no longer say that? (maybe you have this covered, that's just the first thing that popped into my head...) — FreeMan, May 28 '15 at 17:54
@SiddharthRout Doesn't the fact that the `Change` event fired and we're now in the `Worksheet_Change` sub indicate, by default, that some value on the sheet has changed? In that case, there's no need to know what the value was before, we just know that something, somewhere, is different than it was, that's why we're here... — FreeMan, May 28 '15 at 17:55
Check this post: ["How do I get the old value of a changed cell in Excel VBA"](http://stackoverflow.com/questions/4668410/how-do-i-get-the-old-value-of-a-changed-cell-in-excel-vba) — Barranka, May 28 '15 at 17:57
@FreeMan: What I have understood from the question is that OP wants to compare the Old Value with the New value. He doesn't want to know if a cell is changed. A `_Change` event will fire even when you press `F2` in a cell and press `Enter` :) — Siddharth Rout, May 28 '15 at 17:59
@Barranka: I just saw your comment :( I am in a dilemma now whether to delete my answer and close this question as a duplicate or not. The reason is none of the replies there talks about proper use of `_Change` event and `Error Handling` when working with events... I leave it to the community to decide whether to close this as a duplicate or not. — Siddharth Rout, May 28 '15 at 18:19

score 2 · Answer 1 · answered May 28 '15 at 18:06

Further to my comments see this. I have commented the code so you will not have a problem understanding it. But if you do then simply ask. :)

Dim PrevValue As Variant

Private Sub Worksheet_Change(ByVal Target As Range)

    '~~> Check if more than 1 cell is changed
    If Target.Cells.CountLarge > 1 Then Exit Sub

    '~~> Check if the change didn't happen in A1
    If Not Intersect(Target, Range("A1")) Is Nothing Then Exit Sub

    On Error GoTo Whoa

    Application.EnableEvents = False

    '~~> Compare
    If Target.Value <> PrevValue Then
        Range("A1").Value = "Value of " & Target.Address & " changed from " & _
                            PrevValue & " to " & Target.Value

        '~~> Store new value to previous value
        PrevValue = Target.Value
    End If

Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    PrevValue = Target.Value
End Sub

score 0 · Answer 2 · answered May 28 '15 at 18:02

0

You can "temporarily UnDo" to retrieve the original value:

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Where As String, Oldvalue As Variant, NewValue As Variant
   Application.EnableEvents = False
      Where = Target.Address
      NewValue = Target.Value
      Application.Undo
      Oldvalue = Target.Value
      Target.Value = NewValue
   Application.EnableEvents = True

   MsgBox Where & vbCrLf & Oldvalue & vbCrLf & NewValue
End Sub

This is only good for single cells.

answered May 28 '15 at 18:02

Gary's Student

95,722
10
59
99

1

`Worksheet change + Disabling events = Error Handling` :D You may want to see [This](http://stackoverflow.com/questions/13860894/ms-excel-crashes-when-vba-code-runs/13861640#13861640) – Siddharth Rout May 28 '15 at 18:11

Using VBA how do I detect when any value in a worksheet changes?

2 Answers2

Linked