VBA 在单元格值更改时触发宏

VBA trigger macro on cell value change

提问by Kyle Weller

This should be simple. When the value of a cell changes I want to trigger some VBA code. The cell (D3) is a calculation from two other cells =B3*C3. I have attempted 2 approaches:

这应该很简单。当单元格的值发生变化时，我想触发一些 VBA 代码。单元格 (D3) 是来自其他两个单元格的计算=B3*C3。我尝试了两种方法：

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = 4 And Target.Row = 3 Then
    MsgBox "There was a change in cell D3"
  End If
End Sub

Since the cell is a calculation this is not triggered when the value changes, because the calculation remains the same. I also tried:

由于单元格是一个计算，因此当值更改时不会触发它，因为计算保持不变。我也试过：

Private Sub Worksheet_Calculate()
  MsgBox "There was a calculation"
End Sub

But I have multiple calculations on the sheet and it triggers multiple times. Is there a way I can identify which calculation changed on the calculation event? Or is there another way I can track when D3 changes?

但是我在工作表上进行了多次计算，并且多次触发。有什么方法可以确定计算事件中更改了哪个计算？或者还有另一种方法可以跟踪 D3 何时更改？

回答by Chel

Could you try something like this? Change the formula to =D3AlertOnChange(B3*C3).

你能试试这样的吗？将公式更改为=D3AlertOnChange(B3*C3)。

Private D3OldVal As Variant

Public Function D3AlertOnChange(val)
    If val <> D3OldVal Then MsgBox "Value changed!"
    D3OldVal = val
    D3AlertOnChange = val
End Function

回答by Diego

Or try

或者试试

Private Sub Worksheet_Change(ByVal Target As Range)
Dim numdependences As Integer
On Error Resume Next
HasDependents = Target.Dependents.Count
If Err = 0 Then
    If InStr(Target.Dependents.Address, "$D") <> 0 Then
        MsgBox "change"
    End If
End If
On Error GoTo 0
End Sub

You need the error control in case you change a cell that has not dependents.

如果您更改没有依赖项的单元格，则需要错误控制。

回答by walter's human

try this:

尝试这个：

 Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Target.Worksheet.Range("B1")) Is Nothing Then

      Call macro

    End If

 End Sub

looks for a change in value of cell B1, then executes "macro"

寻找单元格 B1 值的变化，然后执行“宏”

回答by Andrew Prostko

If you are only looking at if the Worksheet_Change then it will count a change for anything entered even if it is the same as the previous value. To overcome this I use a Public variable to capture the starting value and compare it.

如果您只查看 Worksheet_Change，那么它会计算输入的任何内容的更改，即使它与之前的值相同。为了克服这个问题，我使用一个公共变量来捕获起始值并进行比较。

This is my code to do this. It also allows you omit parts of the worksheet or you can use it to evaluate every cell in the worksheet.

这是我执行此操作的代码。它还允许您省略工作表的某些部分，或者您可以使用它来评估工作表中的每个单元格。

Place this code in the Worksheet.

将此代码放在工作表中。

Public TargetVal As String 'This is the value of a cell when it is selected


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.CountLarge > 1 Then 'If more then one cell is selected do not save TargetVal. CountLarge is used to protect from overflow if all cells are selected.
    GoTo EXITNOW
Else
    TargetVal = Target 'This sets the value of the TargetVal variable when a cell is selected
End If
EXITNOW:
End Sub

 Sub Worksheet_Change(ByVal Target As Range)
'When a cell is modified this will evaluate if the value in the cell value has changed.
'For example if a cell is entered and enter is pressed the value is still evaluated
'We don't want to count it as a change if the value hasn't actually changed

Dim ColumnNumber As Integer
Dim RowNumber As Integer
Dim ColumnLetter As String

'---------------------
'GET CURRENT CELL INFO
'---------------------
    ColumnNumber = Target.Column
    RowNumber = Target.Row
    ColumnLetter = Split(Target.Address, "$")(1)

'---------------------
'DEFINE NO ACTION PARAMETERS
'   IF CELL CHANGED IS IN NO ACTION RANGE, EXIT CODE NOW FOR PERFORMANCE IMPROVEMENT OR TO NOT TAKE ACTION
'---------------------
    If ColumnNumber <> 4 Then 'This would exempt anything not in Column 4
        GoTo EXITNOW
    ElseIf RowNumber <> 3 Then 'This would exempt anything not in Row 3
        GoTo EXITNOW
    'Add Attional ElseIf statements as needed
    'ElseIf ColumnNumber > 25 Then
        'GoTo EXITNOW
    End If

'---------------------
'EVALUATE IF CELL VALUE HAS CHANGED
'---------------------
Debug.Print "---------------------------------------------------------"
Debug.Print "Cell: " & ColumnLetter & RowNumber & " Starting Value: " & TargetVal & " | New Value: " & Target

    If Target = TargetVal Then
        Debug.Print " No Change"
        'CALL MACRO, FUNCTION, or ADD CODE HERE TO DO SOMETHING IF NOT CHANGED
    Else
        Debug.Print " Cell Value has Changed"
        'CALL MACRO, FUNCTION, or ADD CODE HERE TO DO SOMETHING IF CHANGED
    End If
Debug.Print "---------------------------------------------------------"

EXITNOW:
 End Sub