Detect cell value has changed VBA-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

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.