Search code examples
excelvbaonchangeworksheet-function

VbA Worksheet_Change doesnt recognize DELETE key on cell


simple thing:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, ThisWorkbook.ActiveSheet.Range("C1")) Then
        debug.print "value changed"
        If IsEmpty(Target.Value2) Then
            debug.print "ie empty"
        else
            debug.print "is not empty"
        end if
    end if
end sub

I have added the:

debug.print "value changed"

to see if the "delete" key is triggering the Worksheet_Change() and it does NOT, it is triggered only on changing the value (or add if was empty)! But once there is something, and I select the cell and press DELETE on keyboard, nothing happens :(

what is wrong with the code?


Solution

  • Replace:

    If Intersect(Target, ThisWorkbook.ActiveSheet.Range("C1")) Then
    

    with:

    If Not Intersect(Target, Range("C1")) Is Nothing Then
    

    EDIT#1:

    One possible issue is that If expects a Boolean and Intersect() is returning a Range. Excel is trying to interpret the Range and succeeds some of the time.