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
Application.EnableEvents = False
'--some code
End If
LetsContinue:
Application.EnableEvents = True
Exit Sub
Whoa:
MsgBox Err.Description
Resume LetsContinue
End Sub
Followup
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.
NTN
Sid
source to share