vba 每当单元格更改时，excel VBA都会自动运行宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15337008/
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 VBA run macro automatically whenever a cell is changed
提问by kamelkid2
Is there a simple way to get Excel to automatically execute a macro whenever a cell is changed?
有没有一种简单的方法可以让 Excel 在单元格更改时自动执行宏？
The cell in question would be in Worksheet("BigBoard").Range("D2")
有问题的单元格将在 Worksheet("BigBoard").Range("D2")
What I thought would be a simple Google inquiry is proving to be more complicated - every sample involved intersects (whatever those are) or color formatting or any other number of things that appear to be irrelevant.
我以为是一个简单的谷歌查询被证明是更复杂的 - 每个样本都涉及相交（无论是什么）或颜色格式或任何其他似乎无关的事情。
回答by Peter Albert
Yes, this is possible by using worksheet events:
是的，这可以通过使用工作表事件来实现：
In the Visual Basic Editor open the worksheet you're interested in (i.e. "BigBoard") by double clicking on the name of the worksheet in the tree at the top left. Place the following code in the module:
在 Visual Basic 编辑器中，双击左上角树中的工作表名称，打开您感兴趣的工作表（即“BigBoard”）。将以下代码放入模块中：
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("D2")) Is Nothing Then Exit Sub
Application.EnableEvents = False 'to prevent endless loop
On Error Goto Finalize 'to re-enable the events
MsgBox "You changed THE CELL!"
End If
Finalize:
Application.EnableEvents = True
End Sub
回答by user2140261
Another option is
另一种选择是
Private Sub Worksheet_Change(ByVal Target As Range)
IF Target.Address = "$D" Then
MsgBox("Cell D2 Has Changed.")
End If
End Sub
I believe this uses fewer resources than Intersect, which will be helpful if your worksheet changes a lot.
我相信这比 使用更少的资源Intersect，如果您的工作表变化很大，这将很有帮助。
回答by Zediiiii
In an attempt to find a way to make the target cell for the intersect method a name table array, I stumbled across a simple way to run something when ANY cell or set of cells on a particular sheet changes. This code is placed in the worksheet module as well:
为了找到一种方法使 intersect 方法的目标单元格成为名称表数组，我偶然发现了一种简单的方法，可以在特定工作表上的任何单元格或单元格集更改时运行某些内容。此代码也放置在工作表模块中：
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 0 Then
'mycode here
end if
end sub
回答by David Blackburn
I was creating a form in which the user enters an email address used by another macro to email a specific cell group to the address entered. I patched together this simple code from several sites and my limited knowledge of VBA. This simply watches for one cell (In my case K22) to be updated and then kills any hyperlink in that cell.
我正在创建一个表单，在该表单中，用户输入另一个宏使用的电子邮件地址，将特定单元组通过电子邮件发送到输入的地址。我将来自多个站点的这个简单代码和我对 VBA 的有限知识拼凑在一起。这只是监视要更新的一个单元格（在我的情况下为 K22），然后终止该单元格中的任何超链接。
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("K22")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
Range("K22").Select
Selection.Hyperlinks.Delete
End If
End Sub
回答by Bendaua
In an attempt to spot a change somewhere in a particular column (here in "W", i.e. "23"), I modified Peter Alberts' answer to:
为了发现特定列中某处的变化（此处为“W”，即“23”），我将 Peter Alberts 的回答修改为：
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Target.Column = 23 Then Exit Sub
Application.EnableEvents = False 'to prevent endless loop
On Error GoTo Finalize 'to re-enable the events
MsgBox "You changed a cell in column W, row " & Target.Row
MsgBox "You changed it to: " & Target.Value
Finalize:
Application.EnableEvents = True
End Sub
