vba 当特定单元格中的值发生变化时，如何运行宏？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4792533/
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 can I run a Macro when the value in a specific cell changes?
提问by ZayBear
Let me preface by saying that I am very new to VB...
让我先说我对 VB 很陌生...
I am trying to run a macro whenever the value in a certain cell changes. I've read up on how to do this, but can't seem to get it to work. I have entered the following code into the private module of the worksheet object:
每当某个单元格中的值发生变化时，我都会尝试运行宏。我已经阅读了如何做到这一点，但似乎无法让它发挥作用。我已将以下代码输入到工作表对象的私有模块中：
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Target.Worksheet.Range("$C")) Is Nothing Then
Application.Run _
"'Amex Payments_Experiment.xlsm'!SelectCells"
End If
End Sub
C5 is the cell I am trying to monitor for change. "SelectCells" is the macro I want to run. "Amex Payments_Experiment.xlsm" is the name of the file.
C5 是我试图监视变化的单元格。“SelectCells”是我想要运行的宏。“Amex Payments_Experiment.xlsm”是文件名。
When I change the value in C5 nothing happens. Some help would be great. Thanks!
当我更改 C5 中的值时，什么也没有发生。一些帮助会很棒。谢谢！
UPDATE:
更新：
Cyberkiwi - No, that is not exactly how I did it, but when I follow you're instructions I do find the code where you say it should be. To get to the private module of the worksheet object I right clicked the sheet tab at the bottom, selected "view code", then selected "worksheet" from the dropdown in the top center of the page.
Cyberkiwi - 不，我不是这样做的，但是当我按照您的指示进行操作时，我确实找到了您所说的代码。为了进入工作表对象的私有模块，我右键单击底部的工作表标签，选择“查看代码”，然后从页面顶部中心的下拉列表中选择“工作表”。
User587834 - Yes. Macro's are enabled.
User587834 - 是的。已启用宏。
Any other suggestions?
还有其他建议吗？
回答by DesignFirst
if you use Excel2007 be sure that macros are enabled, by default Excel 2007 deactivate macro execution for new workbook. for that try to execute any other macro to be sure that macros are enabled.
如果您使用 Excel2007，请确保启用宏，默认情况下 Excel 2007 会停用新工作簿的宏执行。为此尝试执行任何其他宏以确保启用宏。
回答by Charles Williams
This code works Ok for me
这段代码对我有用
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Target.Worksheet.Range("$C")) Is Nothing Then
MsgBox "hello"
End If
End Sub
Maybe the problem is with your Application.Run: - select the line containing the intersect and click F9 to switch on Debug, then try changing a cell to see if you get to the sub. If you never reach that line then you have not got the code in the worksheet clkass module, or you have switched off events, or macros are disabled or ...
也许问题出在您的 Application.Run： - 选择包含相交的线并单击 F9 以打开调试，然后尝试更改单元格以查看是否到达子。如果您从未到达该行，那么您还没有在工作表 clkass 模块中获得代码，或者您关闭了事件，或者宏被禁用或...
回答by Patrick Honorez
Check the Application.EnableEventsproperty, and set it to Trueif required.
检查Application.EnableEvents属性，并True根据需要将其设置为。
回答by RichardTheKiwi
I have entered the following code into the private module of the worksheet object:
我已将以下代码输入到工作表对象的私有模块中：
How exactly have you done that? As below?
你是怎么做到的？如下？
- Alt-F11 to switch to code view
- On the left, double-click on the target sheet
- On the right, do you see the code you entered? if not, proceed to next step
- Paste the code block
- Alt-F11 切换到代码视图
- 在左侧，双击目标工作表
- 在右边，你看到你输入的代码了吗？如果没有，继续下一步
- 粘贴代码块
