单元格值更改的VBA触发宏 | 单元格值更改的VBA触发宏

本文介绍了单元格值更改的VBA触发宏的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

这应该很简单.当单元格的值发生变化时，我想触发一些 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触发宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！