Prevent Code from running on cell change

[seguin85]

Hi All,

I have the following code that works great:

Private Sub Worksheet_Change(ByVal target As Range)
If Not Intersect(target, Range("M21:M6000")) Is Nothing Then

On Error Resume Next

If target.Cells.Count > 1 Or IsEmpty(target) Then Exit Sub

    Dim countIN As Integer

    countIN = Application.WorksheetFunction.CountIf(Range("M21:M6000"), "*Inquiry*")

    If target.Cells Like "*Inquiry*" Or target.Cells Like "*inquiry*" Then
    
         target.Cells = "Inquiry " & countIN

    End If

End If

End Sub

The problem is that it seems to run again and again since the target cell is changing. Is there a way to only allow the code to run once?

 

[Scott Huish]

The piece you are missing is Application.EnableEvents you set it to FALSE before the code that changes the sheet and back to TRUE after. You also have an extra End If in there, although I would do the second on one line as well.

Private Sub Worksheet_Change(ByVal target As Range)
If Not Intersect(target, Range("M21:M6000")) Is Nothing Then
On Error Resume Next
If target.Cells.Count > 1 Or IsEmpty(target) Then Exit Sub
Dim countIN As Integer
Application.EnableEvents = False
countIN = Application.WorksheetFunction.CountIf(Range("M21:M6000"), "*Inquiry*")
If target.Cells Like "*Inquiry*" Or target.Cells Like "*inquiry*" Then target.Cells = "Inquiry " & countIN
Application.EnableEvents = True
End Sub

 

[seguin85]

Awesome! Thanks! I just needed to add the enable events to my code.