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
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 = False code End If LetsContinue: = True Exit Sub Whoa: 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
, VBA will not generate any events, and the event
will only run once. Also, you should always set the EnableEvents property back to
to enable the events next time.
source to share