Fire 'Worksheet_Change' event only when cell value is actually different

Question

I wrote code to extract data from the URL a particular page. First time I run the code it extracts data from URL to cell C1.

I am at a point where I want to display a MsgBox whenever cell value changes.

For example:

First time I run the code "Happy" gets extracted to cell C1.
(Cell value changes, so msgbox "value changed")
The second time I run the code then Also "Happy" gets extracted to the cell C1.
(means no change, Noting happens)
The third time I run the code and "Sad" gets extracted to cell C1,
so at this point, I want a msgbox of the cell change.

I tried the below code but it shows the msgbox even when same values are changed in the cell.

For example - Cell contains text "Happy". I rewrite "Happy" in cell and press enter, so it displays msgbox of cell changed despite being same text in the cell.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range

    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("A1:C10")

    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
        ' Display a message when one of the designated cells has been 
        ' changed.
        ' Place your code here.
        MsgBox "Cell " & Target.Address & " has changed."
    End If
End Sub

Your question isn't very clear. You want something to happen when a cell in the specified range changes, right? What should happen? — ashleedawg, Mar 09 '18 at 12:49
@ashleedawg You got it correct . If the cell value changes i want to call a macro . suppose i change value of cell B1 to "Happy" it should run the macro . then if i again Chnage the value of B1 to " happy" it should not do anthing . — Akhil Chaudhary, Mar 09 '18 at 12:54
Oh - if the cell is changed "to the same value" then don't display message... Happy -> Happy is still a change as far as Excel is concerned but there's a way around that... hang on a minute. — ashleedawg, Mar 09 '18 at 12:55
Ahkil - I hope you don't mind, I made your question title a bit more clear — ashleedawg, Mar 09 '18 at 13:32

ashleedawg · Accepted Answer · 2018-03-09T13:31:55.110

This uses Undo to check what the previous value of the cell was, and then compare it to the new value.

This will also not be case-sensitive, so HAPPY = HAPpy. If you want it to be case sensitive then remove the strconv functions.

Note that (any) of these procedures (including yours) will not react properly to multiple cells changing at once (like pasting in a range of cells), but you could add code to handle that however you needed to as demonstrated in the commented out lines.

But for single cells, this will do the trick:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim KeyCells As Range, vNew, vOld

    If Target.Cells.Count > 1 Then
        MsgBox ("multiple cells changed: " & vbLf & Target.Address)
        'to handle multiple cells changing at omce you'll need to loop like:
        ' dim c as cell
        ' for each c in Target.Cells
        ' ... etc
        Exit Sub
    End If

    Set KeyCells = Range("A1:C10") ' cells to watch

    If Not Application.Intersect(KeyCells, Target) Is Nothing Then

        vNew = Target.Value
        Application.EnableEvents = False
        Application.Undo
        vOld = Target.Value
        Target.Value = vNew
        Application.EnableEvents = True

        'make sure value is different (NOT case sensitive)
        If StrConv(vNew, vbLowerCase) <> StrConv(vOld, vbLowerCase) Then

            'do something here
            MsgBox "Cell " & Target.Address & " changed" & vblf & _
                "From: " & vOld & vblf & _
                "To:   " & vNew

        End If
    End If
End Sub

More Information:

MSDN : Application.Undo Method (Excel)
MSDN : Application.EnableEvents Property (Excel)
MSDN : Worksheet.Change Event (Excel)
Stack Overflow : How do I get the old value of a changed cell in Excel VBA? (Ronnie Dickson's answer)

@AkhilChaudhary: If you are happy with the answer, please don't forget to mark it as accepted for 2 reasons: 1. It closes the query. 2. Person who provided the answer gets credited for the answer — Zac, Mar 09 '18 at 13:14

Alex de Jong · Answer 2 · 2018-03-09T13:13:04.820

1

Try it like this:

Public PrevValue

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Value <> PrevValue Then
    MsgBox ("value changed")
    PrevValue = Target.Value
End If
End Sub

The previous value is now stored in the global variable. When the value changes, it first checks if the value is the same as the previous value.

Edit: If you change different cells each time, you can also use

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
PrevValue = Target.Value
End Sub

To set the value of the currenctly selected cell before the change.

edited Mar 09 '18 at 13:13

answered Mar 09 '18 at 12:58

Alex de Jong

1,257
1
11
23

That will only work if the same cell is changed twice in a row, correct? or, two cells changed to the same value in a row. – ashleedawg Mar 09 '18 at 13:08
You are right! Additional code is necessary for mulitple cells. Changed the answer:) – Alex de Jong Mar 09 '18 at 13:13
I assume you're saying the order of events is: `selectionchange` > *(the cell actually changes)* > `change` ? – ashleedawg Mar 09 '18 at 13:19
Yes exactly. But anyhow, I like your idea better. Never thought about using undo. – Alex de Jong Mar 09 '18 at 13:22
Funny, I like your idea better! – ashleedawg Mar 09 '18 at 13:25
@akhil-chaudhary - I changed the answer slightly so it displays before/after values. – ashleedawg Mar 09 '18 at 13:33
Both the `Worksheet_SelectionChange` and `Worksheet_Change` alterations are required to ensure it works. I use this on a per sheet basis (using a `Private pLastValue`) in one of my workbooks, where the change may happen in a drop list and the user may not exit the cell between changes. – AJD Mar 09 '18 at 21:55
One word of caution is that the variant `PrevValue` can overflow. This can be fixed by using an `On Error` routine that sets the value to "" as a soft-fail default. – AJD Mar 09 '18 at 21:57

score 1 · Answer 3 · answered Mar 09 '18 at 12:58

1

Replace:

If Not Application.Intersect(KeyCells, Range(Target.Address))

with:

If Not Application.Intersect(KeyCells, Target)

answered Mar 09 '18 at 12:58

Gary's Student

95,722
10
59
99

That still fires if the value is replaced with the identical value. – ashleedawg Mar 09 '18 at 13:07
what Gary's Student meant to say is that `Range(Target.Address)` does not make much sense because `Target` is already a range. So you try to make a address out of a range to make a range out of an address. – Pᴇʜ Mar 09 '18 at 13:08
@ashleedawg **You are correct.** If you update a cell by re-entering the same value, the Event will be triggered. – Gary's Student Mar 09 '18 at 13:15
1

@Pᴇʜ - I wondered that but I'm surprised he left it as an Answer. Probably an oversight. (I even tested it to see if there was secret functionality I was unaware of!) – ashleedawg Mar 09 '18 at 13:16

Fire 'Worksheet_Change' event only when cell value is actually different

3 Answers3

More Information: