vba Excel Worksheet_Change 在宏更改单元格时不起作用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12754931/
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
Excel Worksheet_Change not working when macro changes cell
提问by JKony
I'm trying to finish up a project of mine and I right now have a Form Control that when pressed adds to the value of a number and another button will subtract that value.
我正在尝试完成我的一个项目，我现在有一个表单控件，当按下它时会增加一个数字的值，另一个按钮将减去该值。
Another value has the two different buttons for the same thing, but the value is also dependent on the first value and other things than just the buttons modify that value. I tried implementing this code for validation
另一个值具有用于同一事物的两个不同按钮，但该值还取决于第一个值和其他事物，而不仅仅是按钮修改该值。我尝试实现此代码进行验证
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Target.Worksheet.Range("F19")) Is Nothing Then
If Range("E19") = 2 And Range("F19") < 12 Then
Range("E20") = 1
End If
End If
End Sub
but Excel apparently doesn't recognize that cell F19 has changed when the change is caused by the button, only when it is caused by user input. So, what this is saying is, if F19 updates and 19 is 2 and F19 is less than 12 (the prerequisite for E12 being 2 is F19 being 12 or greater) then set E20 to 1 (E20 is a modifier for E19 which also has other modifiers going into it). This method works on other values that aren't button controlled, but how can I get excel to realize when the Form Control button changes the value (or at least monitor when the form control is pressed.)
但 Excel 显然无法识别单元格 F19 在更改是由按钮引起的情况下发生了更改，只有当它是由用户输入引起时。所以，这就是说，如果 F19 更新且 19 为 2 且 F19 小于 12（E12 为 2 的先决条件是 F19 为 12 或更大），则将 E20 设置为 1（E20 是 E19 的修饰符，它也有其他修饰符进入它）。此方法适用于不受按钮控制的其他值，但是当表单控件按钮更改值时（或至少在按下表单控件时进行监视），我如何才能让 excel 实现。
Edit: The macro actually doesn't work if the cell changes by formula either. I don't think I can use Worksheet_Calculate to monitor the change in a specific cell.
编辑：如果单元格也按公式更改，则宏实际上不起作用。我不认为我可以使用 Worksheet_Calculate 来监视特定单元格的变化。
回答by Daniel
Why are you doing this with code anyway? You could have Cell E20 have a formula like: =IF(AND(E19=2,F19<12),1,"")which would make the cell blank unless the condition is met.
你为什么用代码做这个？您可以让单元格 E20 具有如下公式：=IF(AND(E19=2,F19<12),1,"")除非满足条件，否则会使单元格为空白。
If you really want to do it with code, you should take this into account: The Worksheet_Change event"Occurs when cells on the worksheet are changed by the user or by an external link."
如果你真的想用代码来做，你应该考虑到这一点：Worksheet_Change 事件“在用户或外部链接更改工作表上的单元格时发生。”
I would recommend instead of having
我会推荐而不是
If Range("E19") = 2 And Range("F19") < 12 Then
Range("E20") = 1
End If
In your Worksheet_Change event that you add it as a separate sub, that you call from Worksheet_Change. You would also call the sub from the code for your button, after you've performed whatever action your button does. That way, you're guaranteed the check gets run and do not try to rely on events.
在您的 Worksheet_Change 事件中，您将其添加为一个单独的子项，您从 Worksheet_Change 调用该子项。在您执行按钮执行的任何操作后，您还可以从按钮的代码中调用 sub。这样，您就可以保证检查得到运行并且不要试图依赖事件。
回答by chris neilsen
Daniel is partialy right, Worksheet_Change"Occurs when cells on the worksheet are changed by the user or by an external link." But this includeschanges caused by VBA, and Excludeschanges by a formula.
Daniel 是部分正确的，Worksheet_Change“当用户或外部链接更改工作表上的单元格时发生。” 但这包括由 VBA 引起的更改，并排除由公式引起的更改。
Your problem may be caused (or at least exacerbated) by the only partial qualification of your ranges:
您的问题可能是由您的范围的唯一部分限定引起（或至少加剧）：
Range("E19")will refer to 'E19' on the active sheet, which may or may not be be the sheets Targetis on. You got it right with Target.Worksheet.Range("F19")
Range("E19")将引用活动工作表上的“E19” ，这可能是也可能不是工作表Target已打开。你做对了Target.Worksheet.Range("F19")
Try (note the .'s)
尝试（注意.'s）
With Target.Worksheet
If Not Intersect(Target, .Range("F19")) Is Nothing Then
If .Range("E19") = 2 And .Range("F19") < 12 Then
.Range("E20") = 1
End If
End If
End With
BTW. I'm with Daniel in that your whole solution seems a little off, but we may not be getting the whole picture...
顺便提一句。我和丹尼尔在一起，因为你的整个解决方案似乎有点不对劲，但我们可能无法了解全貌......
