vba 单元格值更改时自动运行宏（用户未更改）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20275490/
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
Run macro automatically when cell value changed (not changed by user)
提问by MightyMouseZ
Is there a way to have a macro changed when the value is changed is changed, not necessarily when a user changes the value?
有没有办法在更改值时更改宏，而不必在用户更改值时更改？
For example, I have a check box that is linked to a cell "A1". When the check box is checked, A1 reads "TRUE" when it is not checked it reads "FALSE". When A1 changes between TRUE and FALSE I want a macro to run.
例如，我有一个链接到单元格“A1”的复选框。选中复选框时，A1 显示为“TRUE”，未选中时显示为“FALSE”。当 A1 在 TRUE 和 FALSE 之间变化时，我希望运行一个宏。
I have seen the Private Sub Worksheet_Change code, but that seems to only work when a user actually changes cell A1. Is there another way to automatically run the macro?
我已经看到了 Private Sub Worksheet_Change 代码，但这似乎只有在用户实际更改单元格 A1 时才有效。还有另一种方法可以自动运行宏吗？
UPDATE 11/29/13Thank you everyone for your suggestions, but unfortunately it isn't quite working out. Let me try to expand further.
2013 年 11 月 29 日更新感谢大家的建议，但不幸的是它并没有完全奏效。让我尝试进一步扩展。
I have a user form with check boxes, drop down lists, and text fields. All of the data from the user form is summarized in column B of a worksheet (which is where all the user form fields are linked). When the macro runs, some calculations happen and the user get's some numbers.
我有一个带有复选框、下拉列表和文本字段的用户表单。来自用户表单的所有数据都汇总在工作表的 B 列中（所有用户表单字段都在这里链接）。当宏运行时，会发生一些计算，用户会得到一些数字。
When the user changes something in the user form I want the macro to run automatically. I know there is a possibility that this can become resource intensive, but I'll deal with that problem when it comes to it. I would like to have a procedure that says if the value of any cell in range B1:B20 changes then run the macro. I believe this method is easier than telling every user form field to run the same macro because it will be easier to expand and update this form later, especially if someone else takes over maintenance.
当用户更改用户表单中的某些内容时，我希望宏自动运行。我知道这可能会成为资源密集型的问题，但是当涉及到这个问题时，我会处理这个问题。我想要一个程序，说明如果 B1:B20 范围内的任何单元格的值发生变化，然后运行宏。我相信这种方法比告诉每个用户表单字段运行相同的宏更容易，因为以后扩展和更新这个表单会更容易，特别是如果其他人接管维护。
I could just have a button that the user can click to run the macro when they are done filling in the form, but I can foresee inaccurate information because the user forgets to recalculate; this is why I would like to have it done automatically and numbers are updated in real time.
我可以有一个按钮，用户可以在填写完表单后单击它来运行宏，但是我可以预见不准确的信息，因为用户忘记重新计算；这就是为什么我希望它自动完成并实时更新数字。
回答by Delbert Aud
From what I can tell you must enumerate the cells that could be changed or act upon any cell change. Here is a sample function.
据我所知，您必须枚举可以更改的单元格或对任何单元格更改采取行动。这是一个示例函数。
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A" or Target.Address = "$A" Then
Sheet1.Range("A3").Interior.Color = RGB(255, 0, 0)
End If
End Sub
回答by Roger Barreto
Use "Application.Volatile" in the first line of your VBA function
在 VBA 函数的第一行使用“Application.Volatile”
Function A()
Application.Volatile
End Function
回答by Patrick Lepelletier
Worksheet_Change works ALWAYS, but you need to have application.enableevents=true or it wont. But i don't like the if target.address part, i prefer :
Worksheet_Change 始终有效，但您需要具有 application.enableevents=true 否则它不会。但我不喜欢 if target.address 部分，我更喜欢：
Private Sub Worksheet_Change(ByVal Target As Range)
select case target.address 'or target.row, column...
case "$A"
application.enableevents=false 'or the change sub will trigger again on changes!
code
application.enableevents=true
case "$A"
application.enableevents=false 'or the change sub will trigger again on changes!
code
application.enableevents=true
end select
end sub
