Detect cell value has changed VBA

Question

I need to detect with the Workbook_SheetChange that the value of a cell has changed.

This event triggers also when double-clicking any cell. I want to avoid this case because when doing that, the cell has not really changed until you type a new value.

There is any way to avoid this case inside that event?

`Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)` Trap it before? — Nathan_Sav, Oct 15 '21 at 11:36
Try this: https://www.toolbox.com/tech/programming/question/cell-change-event-in-vba-091409/ — David Wooley - AST, Oct 15 '21 at 11:44
You can check that using 'Change` event, too. It will only fill a variable value to compare the previous `Target` one. — FaneDuru, Oct 15 '21 at 11:52
Possible duplicate: https://stackoverflow.com/questions/4668410/how-do-i-get-the-old-value-of-a-changed-cell-in-excel-vba — Jerry Jeremiah, Mar 21 '23 at 22:07

FaneDuru · Accepted Answer · 2021-10-15T12:31:02.300

2

Please, copy the next two events and a Private variable in the sheet code module:

Option Explicit

Private PrevVal As String

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.cells.count > 1 Then Exit Sub
   If CStr(Target.Value) = PrevVal Then Exit Sub
   Debug.Print "Changed..."
   'your code in case of a real change...
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.cells.count > 1 Then Exit Sub
    PrevVal = CStr(Target.Value)
End Sub

The SelectionChange event will place the selected cell value in PrevVal variable, which will be used to compare the previous value with the 'resulted' one in Change event.

It will not treat the cases of multiple cells selection. In some circumstances, the previous value(s) is not possible to be memorized. When a multiple cells range is copied over an existing range, selecting only one cell...

It can be adapted to treat this case, only if the exact range to be overwritten by the pasting to previously be selected.

edited Oct 15 '21 at 12:31

answered Oct 15 '21 at 12:01

FaneDuru

38,298
4
19
27

@Enrique González Didn't you find some time to test the above suggested solution? If tested, didn't it solve your problem? – FaneDuru Oct 16 '21 at 10:42
I have tried and the event SelectionChange is never called, I do not know why, events are enabled for sure. Could it be Excel version? I am using Excel 2016. I – Enrique González Oct 18 '21 at 08:12
@Enrique González Do you mean that all the other events are triggered, except `SelectionChange`? This cannot be possible in any version... If you have other active events and used `Application.EnableEvents = False`, followed by `Application.EnableEvents = True` at the code end and an error occurs between the two line, the events could remain not enabled. Please, run a single code line in a testing sub: `Application.EnableEvents = True` and play with events after that... – FaneDuru Oct 18 '21 at 08:24
I have solve it using Workbook_SheetBeforeDoubleClick, i do not know why but Worksheet_SelectionChange or Worksheet_BeforeDoubleClick was not triggering. – Enrique González Oct 18 '21 at 08:50

score 1 · Answer 2 · answered Oct 15 '21 at 12:05

1

You can use a combination of Worksheet_BeforeDoubleClick & Worksheet_Change to check if the cell value has really changed or it was simply double-clicked without any change

Option Explicit

Private oldValue As String

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   oldValue = Target.Value
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Value <> oldValue Then
      MsgBox "Value is Updated"
   End If

End Sub

answered Oct 15 '21 at 12:05

Gangula

5,193
4
30
59

The event BeforeDoubleClick does not trigger, i do not know why – Enrique González Oct 18 '21 at 08:13
You need to place the code inside the Worksheet module. Not as a custom module. – Gangula Oct 18 '21 at 09:14

Detect cell value has changed VBA

2 Answers2