VBA触发单元格值更改的宏-Java 学习之路

这应该很简单 . 当单元格的值发生变化时，我想触发一些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

由于单元格是计算，因此在值更改时不会触发，因为计算保持不变 . 我也尝试过：

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

但我在工作表上有多个计算，它会多次触发 . 有没有办法可以确定哪个计算在计算事件中发生了变化？还是有另一种方法可以跟踪D3何时发生变化？

4 回答

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

Private D3OldVal As Variant

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

回复于 2024-05-12T18:00:19+08:00

或者试试

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$3") <> 0 Then
        MsgBox "change"
    End If
End If
On Error GoTo 0
End Sub

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

回复于 2024-05-12T18:00:19+08:00

试试这个：

Sub Worksheet_Change(ByVal Target As Range)

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

      Call macro

    End If

 End Sub

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

回复于 2024-05-12T18:00:19+08:00

如果您只查看Worksheet_Change，那么即使它与之前的值相同，它也会对输入的任何内容进行更改 . 为了解决这个问题，我使用Public变量来捕获起始值并进行比较 .

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

将此代码放在工作表中 .

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

回复于 2024-05-12T18:00:19+08:00

VBA触发单元格值更改的宏

4 回答

相关问题