vba 如何使用 Worksheet_change 事件更改单元格值而不触发第二次调用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28220333/
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
How to change cell value using Worksheet_change event without triggering a second call
提问by Barranka
I'm working on a simple worksheet, and I need to change some data in the cells depending on the user input; those changes are made using the Worksheet_Changeevent. However, when I change another cell, the event is triggered again, so it becomes quite a headache (it's kind of a "chicken-and-egg" scenario).
我正在处理一个简单的工作表，我需要根据用户输入更改单元格中的一些数据；这些更改是使用Worksheet_Change事件进行的。但是，当我更改另一个单元格时，该事件再次触发，因此变得非常令人头疼（这是一种“鸡与蛋”场景）。
Example:
例子：
private sub Worksheet_Change(ByVal Target as Range)
with target ' Only cells in column C are unlocked and available for edition
select case .row
case 4
if .value = 1 then
ActiveSheet.cells(5,3).value = 0
else
ActiveSheet.cells(5,3).value = 1
end if
case 5
if .value = 1 then
ActiveSheet.cells(4,3).value = 0
else
ActiveSheet.cells(4,3) = 1
end
end select
end with
end sub
As you can see, changes in row 4 trigger changes in row 5, which may trigger another change in row 4... and it becomes an "infinite call", which eventually crashes excel.
如您所见，第 4 行的更改会触发第 5 行的更改，这可能会触发第 4 行的另一个更改……并成为“无限调用”，最终导致 excel 崩溃。
So, the question is: Is there a way to programmaticallychange the value of a cell without triggering the Worksheet_Changeevent?
所以，问题是：有没有办法在不触发事件的情况下以编程方式更改单元格的值Worksheet_Change？
回答by Gary's Student
Disableinterrupts while changing cells, then re-enablethem when you are done:
在更改单元格时禁用中断，然后在完成后重新启用它们：
Private Sub Worksheet_Change(ByVal Target As Range)
Dim A As Range
Set A = Range("A1")
If Intersect(Target, A) Is Nothing Then Exit Sub
Application.EnableEvents = False
A.Value = ""
A.Offset(0, 1).Value = "CLEARED"
Application.EnableEvents = True
End Sub
