blocks|key|361381|text|是的，这可以通过使用工作表事件来实现：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|361382|在Visual+Basic编辑器中，打开您感兴趣的工作表(即"BigBoard")，双击左上角树中的工作表名称。将以下代码放入模块中：|361383|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|code-block|syntax|javascript|361384|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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:

<pre><code>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
</code></pre>

blocks|key|361424|text|另一种选择是|type|unstyled|depth|inlineStyleRanges|entityRanges|data|361425|Private+Sub+Worksheet_Change(ByVal+Target+As+Range)
++++IF+Target.Address+=+"$D$2"+Then
++++++++MsgBox("Cell+D2+Has+Changed.")
++++End+If
End+Sub|code-block|syntax|javascript|361426|我相信这比Intersect使用的资源更少，如果你的工作表变化很大，这将会很有帮助。|offset|length|style|CODE|361427|entityMap^0|0|0|5|9|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Another option is

<pre><code>Private Sub Worksheet_Change(ByVal Target As Range)
 IF Target.Address = "$D$2" Then
 MsgBox("Cell D2 Has Changed.")
 End If
End Sub
</code></pre>

I believe this uses fewer resources than <code>Intersect</code>, which will be helpful if your worksheet changes a lot.

blocks|key|361503|text|在尝试找到一种方法来使intersect方法的目标单元格成为名称表数组时，我偶然发现了一种简单的方法，可以在特定工作表上的任何单元格或单元格集合发生变化时运行某些内容。此代码也放在工作表模块中：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|361504|Private+Sub+Worksheet_Change(ByVal+Target+As+Range)
If+Target.Cells.Count+>+0+Then
'mycode+here
end+if
end+sub|code-block|syntax|javascript|361505|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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:

<pre><code>Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count &gt; 0 Then
'mycode here
end if
end sub
</code></pre>

blocks|key|143465|text|为了找出特定列(这里是"W"，即"23")中的某处变化，我将Peter+Alberts的答案修改为：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|143466|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|code-block|syntax|javascript|143467|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

In an attempt to spot a change somewhere in a particular column (here in "W", i.e. "23"), I modified Peter Alberts' answer to:

<pre><code>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 " &amp; Target.Row
 MsgBox "You changed it to: " &amp; Target.Value
Finalize:
 Application.EnableEvents = True
End Sub
</code></pre>

blocks|key|143513|text|我正在创建一个表单，用户在其中输入另一个宏使用的电子邮件地址，以便通过电子邮件将特定的小区组发送到输入的地址。我将这段来自多个站点的简单代码与我有限的VBA知识拼凑在一起。这只是监视要更新的一个单元格(在我的例子中是K22)，然后删除该单元格中的任何超链接。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|143514|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|code-block|syntax|javascript|143515|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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.

<pre><code>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
</code></pre>

Is there a simple way to get Excel to automatically execute a macro whenever a cell is changed?

The cell in question would be in <code>Worksheet("BigBoard").Range("D2")</code>

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.

excel VBA run macro automatically whenever a cell is changed

有没有一种简单的方法可以让Excel在单元格发生变化时自动执行宏？有问题的单元应该在Worksheet("BigBoard").Range("D2")中我认为一个简单的Google查询被证明是更复杂的--每个样本都涉及交叉点(不管是什么)，或者颜色格式，或者任何其他看起来不相关的东西。

问每当单元格发生更改时，excel VBA都会自动运行宏
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问每当单元格发生更改时，excel VBA都会自动运行宏EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问每当单元格发生更改时，excel VBA都会自动运行宏
EN