VBA触发宏单元格值更改 [英] VBA trigger macro on cell value change

问题描述

这应该很简单。当单元格的值更改时，我想触发一些VBA代码。单元格（D3）是来自另外两个单元格 = B3 * C3 的计算。我尝试了两种方法：

This should be simple. When the value of a cell changes I want to trigger some VBA code. The cell (D3) is a calculation from two other cells =B3*C3. I have attempted 2 approaches:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = 4 And Target.Row = 3 Then
    MsgBox "There was a change in cell D3"
  End If
End Sub

由于单元格是一个计算，当值更改时，不会触发该单元格，因为计算保持不变。我还试过：

Since the cell is a calculation this is not triggered when the value changes, because the calculation remains the same. I also tried:

Private Sub Worksheet_Calculate()
  MsgBox "There was a calculation"
End Sub

但是我在工作表上有多个计算，它会多次触发。有没有办法我可以确定哪些计算在计算事件上改变？还有另外一种方法可以跟踪D3何时改变？

But I have multiple calculations on the sheet and it triggers multiple times. Is there a way I can identify which calculation changed on the calculation event? Or is there another way I can track when D3 changes?

推荐答案

你能尝试这样吗？将公式更改为 = D3AlertOnChange（B3 * C3）。

Could you try something like this? Change the formula to =D3AlertOnChange(B3*C3).

Private D3OldVal As Variant

Public Function D3AlertOnChange(val)
    If val <> D3OldVal Then MsgBox "Value changed!"
    D3OldVal = val
    D3AlertOnChange = val
End Function

这篇关于VBA触发宏单元格值更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！