每当一个单元格获得的值被公式改变时，我如何运行VBA代码？ [英] How can I run a VBA code each time a cell get is value changed by a formula?

问题描述

我想知道如何在每次单元格获取值更改公式时，如何运行VBA代码？ Ive设法运行一个代码，当一个单元格获得其值被用户更改，但它不起作用

解决方案

如果我有一个公式在单元格A1（例如= B1 * C1），我想运行一些VBA代码每次A1更改由于更新单元格B1或C1，那么我可以使用以下：

  Private Sub Worksheet_Calculate（）
 Dim target As Range 
设置target = Range（A1）
 
如果不相交（target，Range（A1））Not Not Then然后
 //运行我的VBA代码
 End If 
 End Sub 
  / pre> 
 
 
 
 
 
 更新 
 
 
 
据我所知， Worksheet_Calculate 的问题是它在电子表格上包含公式的所有单元格触发，您无法确定哪个单元格已被重新计算（即 Worksheet_Calculate 不提供目标对象）
 
 
 
绕过这个，如果你有一堆的列A中的公式，并且您想要确定哪一个已更新，并向该特定单元格添加注释，那么我认为以下代码将实现：
  Private Sub Worksheet_Change（ByVal Target As Range）
 Dim updatedCell As Range 
 Set updatedCell = Range（Target.Dependents.Address）
 
如果不相交（ updatedCell，Range（A：A））没有，然后
 updatedCell.AddComment（我的评论）
如果
 
 End Sub 
  
要解释，要更新公式，该公式中的一个输入单元格必须更改，例如如果 A1 中的公式是 = B1 * C1 则 B1 或 C1 必须更改为更新A1。 
 
 
 
我们可以使用 Worksheet_Change 事件来检测s / sheet上的单元格更改，然后使用Excel的审核功能跟踪家属，例如单元格A1依赖于 B1 和 C1 ，在这种情况下，代码目标.Dependents.Address 将返回 $ A $ 1 ，以便对 B1 或 C1 。
 
 
 
鉴于此，我们现在需要做的是检查依赖地址是否在列A中（使用相交）。如果在列A中，我们可以向相应的单元格添加注释。
 
 
 
请注意，这仅适用于将注释添加到单元格中。如果您想继续覆盖同一个单元格中的注释，则需要修改代码以首先检查注释的存在，然后根据需要进行删除。
 
I would like to know how can I run a VBA code each time a cell get is value changed by a formula?? Ive managed to run a code when a cell gets its value changed by the user, but it doesn't work w
解决方案
If I have a formula in cell A1 (e.g. = B1 * C1) and I want to run some VBA code each time A1 changes due to updates to either cell B1 or C1 then I can use the following:
Private Sub Worksheet_Calculate()
    Dim target As Range
    Set target = Range("A1")

    If Not Intersect(target, Range("A1")) Is Nothing Then
    //Run my VBA code
    End If
End Sub




Update

As far as I know the problem with Worksheet_Calculate is that it fires for all cells containing formulae on the spreadsheet and you cannot determine which cell has been re-calculated (i.e. Worksheet_Calculate does not provide a Target object)

To get around this, if you have a bunch of formulas in column A and you want to identify which one has updated and add a comment to that specific cell then I think the following code will achieve that:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim updatedCell As Range
    Set updatedCell = Range(Target.Dependents.Address)

    If Not Intersect(updatedCell, Range("A:A")) Is Nothing Then
       updatedCell.AddComment ("My Comments")
    End If

End Sub
To explain, for a formula to update, one of the input cells into that formula must change e.g. if formula in A1 is =B1 * C1 then either B1 or C1 must change to update A1. 

We can use the Worksheet_Change event to detect a cell change on the s/sheet and then use Excel's auditing functionality to trace the dependents e.g. cell A1 is dependent on both B1 and C1 and, in this instance, the code Target.Dependents.Address would return $A$1 for any change to B1 or C1.

Given this, all we now need to do is to check if the dependent address is in column A (using Intersect). If it is in Column A we can then add comments to the appropriate cell.

Note that this only works for adding comments once only into a cell. If you want to continue to overwrite comments in the same cell you would need to modify the code to check for the existance of comments first and then delete as required.

                        
这篇关于每当一个单元格获得的值被公式改变时，我如何运行VBA代码？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！