Keep Change Event From Triggering

[CEG]

I have a change event that adds a date in column K when a number is entered in column C.

Private Sub Worksheet_Change(ByVal Target As Range)
'Adds date/time in column "K" when P/N is entered in column "C"
    Application.ScreenUpdating = False
    On Error Resume Next
    If Not Intersect(Target, Range("C:C")) Is Nothing Then
        If Target = "" Then Target.Offset(0, 8).ClearContents
        If Target > 1 Then
            If Target.Offset(0, 8) > 1 Then Exit Sub
           With Target.Offset(0, 8)
                .Value = "=NOW()"
                .Value = .Value
            End With
        End If
    End If
    Application.ScreenUpdating = True
End Sub

I also have a userform that has a text box for searching for a number that is located in column C.

Private Sub tbPartNo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Application.EnableEvents = False
    If KeyCode = 13 Then
        Dim strFind As String   'what to find
        Dim rSearch As Range  'range to search
        Dim c As Range
        Set rSearch = Range("part_number")
        strFind = tbPartNo.Value    'what to look for
        With rSearch
            Set c = .Find(strFind, LookIn:=xlValues)
            If Not c Is Nothing Then    'found it
                c.Select
            End If
        End With
        Call UserForm_Initialize
        tbPartNo.SetFocus
    End If
    Application.EnableEvents = True
End Sub

When I search, it seems to be triggering the change event, which then takes longer and also updates the screen multiple of times. Anyway around this?

 

[chuckchuckit]

See next post - I goofed here...

 

[chuckchuckit]

If there is a possibility of your change even being triggered due to something being entered in col C, just first turn off that capability with code:

Application.EnableEvents = False 'turn off change events triggering
 
    'Run whatever your code is here
 
Application.EnableEvents = True 'be sure to turn back on