VBA: Change Event Not Firing

Question

if any cell in column k has a value of "Yes" then offset (0,1) become empty, Else if any cell in column k has a value of "No" then offset (0,2) become empty. The problem is that the event is not firing. The code is placed in the worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)

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

    On Error GoTo safe_exit

    Application.EnableEvents = False

    Dim Cell As Range
    For Each Cell In Intersect(Target, Range("k:k"))
        If Cell.Value = "Yes" Then

            Cell.Offset(0, 1).ClearContents
           
        ElseIf Cell.Value = "No" Then
            Cell.Offset(0, 2).ClearContents
        End If
    Next Cell

End If

safe_exit:
Application.EnableEvents = True

End Sub

Code works fine for me. **Entering** Yes or No into column K of the target sheet results in the cells being wiped. You're not expecting the event to fire based on the result of a formula in column K changing are you? — CLR, Aug 25 '20 at 07:20
Again, the code works for me with a "Yes,No" validation list. Couple of things to check.. your list doesn't contain any spaces or mispellings? Also, have you checked that `Application.EnableEvents` currently `= True`? — CLR, Aug 25 '20 at 07:28
Also, this code is in the Worksheet code object, and not just in a module, yes? — CLR, Aug 25 '20 at 07:32
You were right the problem wasn't the code itself. While debugging "The current state" of Application.EnableEvents was not switched back to enabled. — Zatary, Aug 25 '20 at 08:35
I doubt any of us can honestly say we've never fallen into that trap before. Glad it's sorted. — CLR, Aug 25 '20 at 11:00

score 0 · Accepted Answer · answered Aug 25 '20 at 08:36

0

The Code were correct. While debugging "The current state" of Application.EnableEvents was not switched back to enabled. Thanks everyone for the contributions. CLR & Tim Williams

answered Aug 25 '20 at 08:36

Zatary

79
7

VBA: Change Event Not Firing

1 Answers1