vba 单元格值更改时宏不会自动运行

Macro does not automatically run when cell value changes

提问by DLittlejohn412

I would like to run a macro when a value in cell F1is updated by the calculation =DAYS360(B2,B1)

当单元格F1 中的值通过计算更新=DAYS360(B2,B1)时，我想运行一个宏

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$F" Then
    MsgBox "You changed THE CELL!"
End If
End Sub

There are several topics on this, and none of the solutions for them are working for me.. I am baffled. If I manually change the value in cell F1, the macro runs. However, when I add a new value in the date column that causes the calculation in cell F1to update, nothing happens.

关于这个有几个主题，但没有一个解决方案对我有用..我很困惑。如果我手动更改单元格F1 中的值，宏将运行。但是，当我在日期列中添加一个新值导致单元格F1 中的计算更新时，没有任何反应。

Some of the other topics offer solutions using the intersectfunction, however, those are giving me the same results.

其他一些主题提供了使用intersect函数的解决方案，但是，这些给了我相同的结果。

回答by user2140261

You could change your Change Event to look for the cells that F1 is looking at.

您可以更改更改事件以查找 F1 正在查看的单元格。

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B" Or Target.Address = "$B" Then
    MsgBox "You changed THE CELL!"
End If
End Sub

This makes perfect sense because these are actually the cells that you are changing, i caught this from your line when I add a new value in the date column that causes the calculation in cell F1 to update

这是完全有道理的，因为这些实际上是您正在更改的单元格，我从您的线路中捕捉到了这一点 when I add a new value in the date column that causes the calculation in cell F1 to update

Alternativly if your formula references will change you can use the below code:

或者，如果您的公式引用会改变，您可以使用以下代码：

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
For Each r In Range("F1").Precedents
    If Target.Address = r.Address Then
        MsgBox "You changed THE CELL!"
        Exit For
    End If
Next r
End Sub

What the above code will do is whenever any cell in the worksheet is changed, it will goto cell F1, then look to see if the cell that was changed was one of the cells the F1 relies on for its calculation, if it wasn't it does nothing, if it was, than it will fire the msgbox.

上面的代码所做的是，每当工作表中的任何单元格发生更改时，它都会转到单元格 F1，然后查看更改的单元格是否是 F1 计算所依赖的单元格之一，如果不是它什么都不做，如果是的话，它会触发 msgbox。

UPDATE:Another option that will work across all the sheets to see if any cell changed is relied on by the formula inside the Cell F1 on Sheet1 when the cells are on another page can be found below.

更新：当单元格在另一页上时，另一个可以在所有工作表中工作以查看是否有任何单元格更改的选项依赖于 Sheet1 上的单元格 F1 内的公式。

Public Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
On Error GoTo NextStep
Dim r As Range
For Each r In Sheets("SheetF1IsOn").Range("F1").Precedents
    If Target.Address = r.Address Then
        MsgBox "You changed THE CELL!"
        Exit For
        Exit Sub
    End If
Next r

NextStep:
If InStr(Replace(Sheets("SheetF1IsOn").Range("F1").Formula, "'", ""), sh.Name + "!" + Target.Address) _
   Or InStr(Replace(Sheets("SheetF1IsOn").Range("F1").Formula, "'", ""), sh.Name + "!" + Target.Address(False)) _
   Or InStr(Replace(Sheets("SheetF1IsOn").Range("F1").Formula, "'", ""), sh.Name + "!" + Target.Address(, False)) _
   Or InStr(Replace(Sheets("SheetF1IsOn").Range("F1").Formula, "'", ""), sh.Name + "!" + Target.Address(False, False)) > 0 Then
        MsgBox "You changed THE CELL!"
End If
End Sub

If it needs to work both on other sheets and the current sheet, then you will have to add another if test for target in the formula then see if it is preceded by the name of a sheet in the workbook and a !

如果它需要在其他工作表和当前工作表上工作，那么您将不得不在公式中添加另一个 if 测试目标，然后查看它是否以工作簿中工作表的名称和 !

UPDATE2:Both the above are slightly flawed (as Simoco has pointed out) the last thing I can come up with is to use a public variable, to set the value of your cell and every time the Work Sheet is calculated to test if the value changed. (if doing a whole column you may need to Dim a collection or array to test against)

UPDATE2：以上两者都有轻微缺陷（正如 Simoco 指出的那样）我能想到的最后一件事是使用公共变量，设置单元格的值，以及每次计算工作表以测试值改变了。（如果做一整列，你可能需要 Dim 一个集合或数组来测试）

So inside you worksheet module, replace your original code with the following:

因此，在您的工作表模块中，将原始代码替换为以下内容：

Option Explicit
Public F1ValueOld As Variant

Private Sub Worksheet_Calculate()
    If Range("F1").Value <> F1ValueOld Then
        MsgBox "You changed THE CELL!"
        F1ValueOld = Range("F1").Value
    End If
End Sub

This will work while the workbook is open, but you will get an error when you close and reopen the workbook, so inside the Workbook module add the following code also:

这将在工作簿打开时起作用，但是当您关闭并重新打开工作簿时会出现错误，因此在工作簿模块中也添加以下代码：

Private Sub Workbook_Open()
    F1ValueOld = Sheets("SheetF1IsOn").Range("A1").Value
End Sub

This should no accomplish what you are attempting.

这不应该完成您正在尝试的操作。