vba Worksheet_Change 事件给出运行时错误 13 - 插入行时类型不匹配
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9827060/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Worksheet_Change Event giving run-time error 13 - Type Mismatch When Inserting Rows
提问by rryanp
I'm using Excel VBA and have a worksheet_change event like so:
我正在使用 Excel VBA 并有一个 worksheet_change 事件，如下所示：
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("rel_type") Then
--some code
End If
End Sub
That code works great when I make a change to the named range "rel_type". However if I insert a row anywhere else in the spreadsheet, I get run-time error 13 - type mismatch on the first line of that Sub. Does anybody know a workaround? I'm not that familiar with the Worksheet_Change event and can't seem to find good documentation (or at least documentation that references why this error would occur). Thanks.
当我更改命名范围“rel_type”时，该代码效果很好。但是，如果我在电子表格的任何其他位置插入一行，我会收到运行时错误 13 - 在该 Sub 的第一行输入不匹配。有人知道解决方法吗？我对 Worksheet_Change 事件不太熟悉，似乎找不到好的文档（或者至少是引用为什么会发生此错误的文档）。谢谢。
回答by Siddharth Rout
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, that's exactly what I needed! Thanks! It worked like a charm. I was not familiar with Intersect or the EnableEvents option, so I learned a lot here--I appreciate it. – rryanp 16 secs ago
是的，这正是我需要的！谢谢！它就像一个魅力。我不熟悉 Intersect 或 EnableEvents 选项，所以我在这里学到了很多东西——我很感激。– ryanp 16 秒前
INTERSECT: The Intersect Method will return a Range Object that represents the intersection of two, or more, ranges.
INTERSECT：Intersect 方法将返回一个范围对象，该对象表示两个或多个范围的交集。
See this link
看这个链接
Topic: Intersect Method [Excel 2003 VBA Language Reference]
主题：相交方法 [Excel 2003 VBA 语言参考]
Link: Intersect - MSDN
链接：相交 - MSDN
ENABLEEVENTS: You have to use the EnableEvents property to prevent any probable endless loops which the VBA code might initiate. When you set this property to False, VBA will not raise any events and the Worksheet_Changeevent will run only once. Also you should always be sure to set EnableEvents property back to Trueto enable events to be called normally the next time.
ENABLEEVENTS：您必须使用 EnableEvents 属性来防止 VBA 代码可能启动的任何可能的无限循环。当您将此属性设置为 时False，VBA 将不会引发任何事件并且该Worksheet_Change事件只会运行一次。此外，您应该始终确保将 EnableEvents 属性设置回以True在下次正常调用事件。
HTH
HTH
Sid
锡德
