SheetChange triggered too many times on cell with DataValidation RRS feed

  • Question

  • I have some functionality that uses the event SheetChange.

    On a cell I have a DataValidation. 

    If the user changes the value of the cell and the DataValidation fails the user has the possibility to change the value. However after the user selects the correct value the SheetChange event is triggered as many times the user tried to change the value.

    Is there a way to prevent the SheetChange to be triggered only once, after the user has selected the correct value? 

    Tuesday, February 2, 2016 9:12 AM


  • Unfortunately no, but we can write some code to skip the superfluous calls.


    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
      Static Last As Range, Value As Variant
      'Just for the demo
      If Target.Count = 1 Then Debug.Print Now, Target.Value
      'Initialize if necessary
      If Last Is Nothing Then Set Last = Target
      'Same cell(s) are last time?
      If Last.Address(0, 0) = Target.Address(0, 0) Then
        'Only one cell?
        If Target.Count = 1 Then
          'Same value?
          If Value = Target.Value Then Exit Sub
          'Save it
          Value = Target.Value
          Value = Empty
        End If
        Value = Empty
      End If
      'Remmeber the last changed cell
      Set Last = Target
      'Just for the demo
      If Target.Count = 1 Then MsgBox Target.Value, , Target.Address
    End Sub

    Tuesday, February 2, 2016 5:25 PM