如何检测单元是否被“ = IF（）”改变？公式，而非用户 [英] How to Detect if a Cell is Changed by an "=IF()" Formula and not by a User

问题描述

我读了很多页面，但如果没有通过 if function手动更改值，他们都没有提出解决方案。

I read a lot of pages saying that, but none of them put the solution if the value change by an "if function" not by hand.

代码我得到的是：

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Me.Range("A18:A30")) 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!"
Finalize:
    Application.EnableEvents = True
End Sub

在此先感谢您。

推荐答案

另一种解决方案；而不是每次重新计算工作表时都触发函数，而是在模块中添加函数：

Another solution; instead of triggering your function every time when your worksheet recalculates, add a function in a module:

Function DetectChange() As Integer
    MsgBox "You changed THE CELL!"
    DetectChange = 0
End Function

假设公式的结果为数字:(否则函数的结果必须为空字符串， +必须为&）
最后添加到IF公式中... + Detectchange（）
现在，是重新计算公式时的仅 msgbox

Assuming the outcome of your formula is numeric:(otherwise outcome of function must be a empty string and the "+" must be "&") Add to your IF-formula at the end ...+Detectchange() Now there will be a msgbox only when your formula is recalculated

编辑（作者：Darren Bartrup-Cook：

Edit by Darren Bartrup-Cook:

我发现重新计算公式时此代码有效。如果我更改了一个不影响其输入的单元格并且使用立即计算 或 Calculate，它也没有触发，则不会触发Sheet 。

偶尔会触发我使用该函数的所有公式，但这似乎是在调试时-可能需要进一步调查。

I found this code gave worked when the formula recalculated. It didn't fire if I changed a cell that doesn't affect the cell it's entered to and it didn't fire using Calculate Now or Calculate Sheet.
It did occasionally fire for all formula that I used the function in, but that seemed to be when I was debugging - maybe further investigation needed.

Public Function DetectChange()
    MsgBox "You changed cell " & Application.Caller.Address
End Function  

例如：

= IF（A1 = 1，A2，A3）&如果更改了单元格A1，A2或A3，则在单元格A4中输入的DetectChange（）会显示消息您更改了单元格$ A $ 4 。

这篇关于如何检测单元是否被“ = IF（）”改变？公式，而非用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！