当单元格值发生变化时自动执行VBA宏

Question

我有一个工作表(sheet1)，其中包含一个带有公式='sheet2'!D10的单元格A1。每当单元格A1在sheet1中发生变化时(由于D10在sheet2中的更改)，我希望运行一个宏。sheet2正在对金融数据进行流化。

因为它是值的更改，所以Worksheet_Change不会触发事件。我似乎也找不到Worksheet_Calculate的解决方案。

在我的研究中，我能找到的最接近的解决方案是提供这里，但我一直未能成功地实现它。

Answer 1

Selection_Change & Change

我走到了另一个方向，迷路了。我认为这里可能有一些有用的东西，所以这是代码。它可能在大多数情况下都能工作，只是失去了'str1‘行。

“str1”行用于调试，并显示单元格在不同条件下的行为。

不确定子ChangeD10是否在模拟您的条件。

认输，但希望能准确指出代码中的错误。

Option Explicit

Private TargetValue As Variant
Private TargetAddress As String

Private Sub Worksheet_Change(ByVal Target As Range)

  'The Playground
  Const cStrWs1 As String = "Sheet1"
  Const cStrWs2 As String = "Sheet2"
  Const cStrCell1 As String = "A1"
  Const cStrCell2 As String = "D10"
  'Other Variables
  Dim oWs1 As Worksheet
  Dim oWs2 As Worksheet
  Dim oRng As Range
  Dim varA1_Before As Variant
  Dim varA1_Now As Variant
  'Debug
  Const r1 As String = vbCr
  Dim str1 As String
  'Initialize
  Set oWs1 = ThisWorkbook.Worksheets(cStrWs1)
  Set oWs2 = ThisWorkbook.Worksheets(cStrWs2)
  Set oRng = oWs2.Range(cStrCell2)
  varA1_Before = oWs1.Range(cStrCell1).Value

            str1 = "Worksheet_Change"

  'Play
  If Target.Address = oRng.Address Then
    If Target.Value <> TargetValue Then
      varA1_Now = oWs2.Range(cStrCell2).Value
      oWs1.Range(cStrCell1).Value = varA1_Now

            str1 = str1 & r1 & Space(1) & "Cell '" & cStrCell2 & "' changed " _
                & "(Target.Value <> TargetValue)" & r1 & Space(2) _
                & "Before: TargetValue (" & TargetAddress & ") = '" _
                & TargetValue & "'," & r1 _
                & "         varA1_Before (" & Range(cStrCell1).Address _
                & ") = " & varA1_Before & "'," & r1 & Space(2) _
                & "Now:   Target.Value (" & Target.Address & ") = '" _
                & Target.Value & "'," & r1 _
                & "            varA1_Now (" & Range(cStrCell1).Address _
                & ") = " & varA1_Now & "'."

     Else

            str1 = str1 & r1 & Space(1) & "Cell '" & cStrCell2 _
                & "' didn't change. TargetValue = '" & TargetValue _
                & "' and Target.Value = '" & Target.Value & "'."

    End If
   Else

            str1 = str1 & r1 & Space(1) & "Cell '" & cStrCell2 _
                & "' not changed. The Target.Address is '" _
                & Target.Address & "', TargetValue is '" & TargetValue _
                & "' and Target.Value is '" & Target.Value & "'."

  End If

            Debug.Print str1

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

  Const r1 As String = vbCr
  Dim str1 As String

            str1 = "Worksheet_SelectionChange"

  If Target.Cells.Count = 1 Then

            str1 = str1 & r1 & Space(1) & "Cell '" & Target.Address _
                & "' selected " & r1 & Space(2) _
                & "Before: TargetValue (" & TargetAddress & ") = '" _
                & TargetValue & "'," & r1 & Space(2) _
                & "Now:    Target.Value (" & Target.Address & ") = '" _
                & Target.Value & "'."

    TargetValue = Target.Value
    TargetAddress = Target.Address

   Else
            str1 = str1 & r1 & Space(1) & "Multiple cells in range '" _
                & Target.Address & "'."
  End If

  Debug.Print str1

End Sub

Sub ChangeD10()
  ThisWorkbook.Worksheets("Sheet2").Cells(10, 4) = 22
End Sub