如何在不触发第二个调用的情况下使用Worksheet_change事件更改单元格值

问题描述

我正在处理一个简单的工作表，并且需要根据用户输入更改单元格中的一些数据；这些更改是使用Worksheet_Change事件进行的.但是，当我更改另一个单元格时，该事件再次被触发，因此非常头痛(这是一种鸡与蛋"的情况).

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_Change event. 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).

示例:

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

如您所见，第4行中的更改会触发第5行中的更改，这可能会触发第4行中的其他更改...，它变成一个无限调用"，最终会导致excel崩溃.

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.

因此，问题是:是否可以通过编程方式更改而不触发Worksheet_Change事件的单元格值?

So, the question is: Is there a way to programmatically change the value of a cell without triggering the Worksheet_Change event?

推荐答案

禁用中断，然后在完成操作后重新启用:

Disable interrupts while changing cells, then re-enable them 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

这篇关于如何在不触发第二个调用的情况下使用Worksheet_change事件更改单元格值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！