excel - 特定单元格更改时如何运行Excel宏？ [新变化]

我四处搜寻，并且编写了以下代码，仅在特定单元格D4发生更改时才要运行：

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    Static EmailSent As Boolean
    Dim Threshold As Integer
    Dim Cell As String, Email As String, Msg As String

    Cell = "D4"
    Threshold = 100
    Email = Range("E7").Value


    Set KeyCells = Range(Cell)

    If Not Application.Intersect(Range(Cell), Range(Target.Address)) Is Nothing Then
        Dim x As Integer
        x = Range(Cell).Value

        If x >= Threshold Then
            EmailSent = False
        ElseIf x < Threshold And Not EmailSent Then
            EmailSent = True
            Msg = "You only have " & x & " widgets remaining."
            MsgBox Msg
            SendMail Email, Msg
        End If
    End If
End Sub

这行得通，我知道这里有很多类似的问题。但是，这就是我遇到的麻烦：仅当我将D4设置为显式值（例如"48"）时，此方法才有效。即使D4是公式，我也希望它能工作：因此，如果D4是"=SUM(A4:C4)"，则当该总和低于100时，应该发送一封电子邮件。在这种情况下，此代码不会发送电子邮件:-(

有谁知道如何解决这一问题？

最佳答案

Private Sub Worksheet_Calculate()
    CheckForMail
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    CheckForMail Target
End Sub


Sub CheckForMail(Optional rng As Range = Nothing)

    Static EmailSent As Boolean
    Dim KeyCells As Range
    Dim Threshold As Integer
    Dim Cell As String, Email As String, Msg As String
    Dim x As Integer

    Cell = "D4"
    Set KeyCells = Me.Range(Cell)

    'if called from worksheet_change, check the range
    If Not rng Is Nothing Then
        If Application.Intersect(KeyCells, rng) Is Nothing Then
            Exit Sub
        End If
    End If

    Threshold = 100
    Email = Me.Range("E7").Value
    x = KeyCells.Value

    If x >= Threshold Then
        EmailSent = False
    ElseIf x < Threshold And Not EmailSent Then
        EmailSent = True
        Msg = "You only have " & x & " widgets remaining."
        MsgBox Msg
        SendMail Email, Msg
    End If

End Sub

关于excel - 特定单元格更改时如何运行Excel宏？ [新变化]，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/11530305/