vba 单元格值更改事件和运行连续宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23367344/
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
Cell Value Change Event and Running a Continuous Macro
提问by alfonso
I want my macro to run automatically when Sheet1 is open AND a value is changed in any of the drop down menus in Column B. I assume I can write a couple of event listener wrappers such as:
我希望我的宏在 Sheet1 打开时自动运行，并且在 B 列的任何下拉菜单中更改了一个值。我假设我可以编写几个事件侦听器包装器，例如：
' pseudocode
While Sheet1 is open
When a dropdown value is changed in column B
Call Update
End When
End While
I've found a few links online, but I don't quite understand. In these links, they have code referring to Target. Is Target a named range? I haven't had any luck implementing these. I'm thinking these links could have an answer to my problem.
http://www.mrexcel.com/forum/excel-questions/95341-running-macro-continuously.html
http://msdn.microsoft.com/en-us/library/office/ff839775(v=office.15).aspx
网上找了几个链接，不是很明白。在这些链接中，他们有引用 Target 的代码。Target 是命名范围吗？我没有任何运气实施这些。我认为这些链接可以解决我的问题。
http://www.mrexcel.com/forum/excel-questions/95341-running-macro-continuously.html
http://msdn.microsoft.com/en-us/library/office/ff839775(v=office.15 ).aspx
Thanks
谢谢
回答by Gary's Student
You can use a worksheet event macro:
您可以使用工作表事件宏：
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rB As Range
Set rB = Range("B:B").Cells.SpecialCells(xlCellTypeAllValidation)
If Intersect(Target, rB) Is Nothing Then
Else
Application.EnableEvents = False
Call Update
Application.EnableEvents = True
End If
End Sub
Because it is worksheet code, it is very easy to install and automatic to use:
因为是工作表代码，所以很容易安装，自动使用：
- right-click the tab name near the bottom of the Excel window
- select View Code - this brings up a VBE window
- paste the stuff in and close the VBE window
- 右键单击 Excel 窗口底部附近的选项卡名称
- 选择查看代码 - 这会打开一个 VBE 窗口
- 粘贴内容并关闭 VBE 窗口
If you have any concerns, first try it on a trial worksheet.
如果您有任何疑虑，请先在试用工作表上尝试。
If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx
如果您保存工作簿，宏将与它一起保存。如果您使用的是 2003 年以后的 Excel 版本，则必须将文件另存为 .xlsm 而不是 .xlsx
To remove the macro:
要删除宏：
- bring up the VBE windows as above
- clear the code out
- close the VBE window
- 调出如上的 VBE 窗口
- 清除代码
- 关闭 VBE 窗口
To learn more about macros in general, see:
要了解有关一般宏的更多信息，请参阅：
http://www.mvps.org/dmcritchie/excel/getstarted.htm
http://www.mvps.org/dmcritchie/excel/getstarted.htm
and
和
http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx
http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx
To learn more about Event Macros (worksheet code), see:
要了解有关事件宏（工作表代码）的更多信息，请参阅：
http://www.mvps.org/dmcritchie/excel/event.htm
http://www.mvps.org/dmcritchie/excel/event.htm
Macros must be enabled for this to work!
必须启用宏才能使其工作！
回答by Jacob Lambert
What you are going to need is a Worksheet_Changed event similar to this:
你需要的是一个类似于这样的 Worksheet_Changed 事件：
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Column("B")) Is Nothing Then
Application.EnableEvents = False
Call Update
Application.EnableEvents = True
End If
End Sub
This will trigger if any changes are made in column B. The Target is supplied by the application event and is the range that had the change made.
如果在 B 列中进行任何更改，这将触发。目标由应用程序事件提供并且是进行更改的范围。
Edit: You will need to put this in the code of the sheet, not a module. If you right click on the sheet tab at the bottom of the excel screen, one of the options is view code.
编辑：您需要将其放在工作表的代码中，而不是模块中。如果您右键单击 Excel 屏幕底部的工作表选项卡，选项之一是查看代码。
