在单元格更改时自动执行 Excel 宏 [英] automatically execute an Excel macro on a cell change

问题描述

如何在每次特定单元格中的值更改时自动执行 Excel 宏?

How can I automatically execute an Excel macro each time a value in a particular cell changes?

现在，我的工作代码是:

Right now, my working code is:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("H5")) Is Nothing Then Macro
End Sub

其中 "H5" 是被监控的特定单元格，Macro 是宏的名称.

where "H5" is the particular cell being monitored and Macro is the name of the macro.

有更好的方法吗?

推荐答案

你的代码看起来不错.

但是请注意，对 Range("H5") 的调用是 Application.Range("H5") 的快捷命令，相当于Application.ActiveSheet.Range("H5").如果唯一的更改是用户更改(这是最典型的)，这可能没问题，但是当工作表不是活动工作表时，它可能会通过编程更改(例如VBA.

Be careful, however, for your call to Range("H5") is a shortcut command to Application.Range("H5"), which is equivalent to Application.ActiveSheet.Range("H5"). This could be fine, if the only changes are user-changes -- which is the most typical -- but it is possible for the worksheet's cell values to change when it is not the active sheet via programmatic changes, e.g. VBA.

考虑到这一点，我将使用 Target.Worksheet.Range("H5"):

With this in mind, I would utilize Target.Worksheet.Range("H5"):

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Target.Worksheet.Range("H5")) Is Nothing Then Macro
End Sub

或者您可以使用 Me.Range("H5")，如果事件处理程序在相关工作表的代码页上(通常是):

Or you can use Me.Range("H5"), if the event handler is on the code page for the worksheet in question (it usually is):

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("H5")) Is Nothing Then Macro
End Sub

希望这有帮助...

这篇关于在单元格更改时自动执行 Excel 宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！