Worksheet_Change Event giving runtime error 13 - Type mismatch when inserting rows

I am using Excel VBA and have a workheet_change event:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target = Range("rel_type") Then
       --some code
    End If
End Sub


This code works great when I make changes to the named range "rel_type". However, if I insert a row somewhere else in the spreadsheet, I get a Type 13 error on the first line of this Sub. Does anyone know a workaround? I am unfamiliar with the Worksheet_Change event and cannot find good documentation (or at least documentation that mentions why this error occurred). Thank.


source to share

1 answer

Is this what you are trying?

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Whoa

    If Not Intersect(Target, Range("rel_type")) Is Nothing Then
       Application.EnableEvents = False

       '--some code
    End If

    Application.EnableEvents = True
    Exit Sub
    MsgBox Err.Description
    Resume LetsContinue
End Sub




Yes, this is exactly what I need! Thank you! It worked like a charm. I was not familiar with the Intersect or EnableEvents option, so I learned a lot here - I appreciate that. - rryanp 16 seconds ago


INTERSECT . The Intersect method will return a range object that represents the intersection of two or more ranges.

See this link

Topic: Navigation Method [Excel 2003 VBA Language Reference]

Link : Intersect - MSDN

ENABLEEVENTS . You should use the EnableEvents property to prevent possible infinite loops that VBA code can initiate. When you set this property to a value False

, VBA will not generate any events, and the event Worksheet_Change

will only run once. Also, you should always set the EnableEvents property back to True

to enable the events next time.





All Articles