VBA trigger macro on cell value change

Question

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:

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?

score 7 · Accepted Answer · answered Aug 29 '13 at 00:59

7

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

Private D3OldVal As Variant

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

answered Aug 29 '13 at 00:59

Chel

2,593
1
18
24

I placed the variant and public function in the vba sheet where the cell is located. I changed the formula to `=D3AlertOnChange(B3*C3)` and it says `#NAME?` Did I place the code in the wrong location? – Kyle Weller Aug 29 '13 at 20:42
2

Try putting it into a standard module. Right-click on any of the sheets and select "Insert -> Module". – Chel Aug 29 '13 at 22:49
While I can tweak this solution to meet my needs, I wanted to mention that this event is triggered before the cell is updated on the sheet, not after. Place a `MsgBox(val)` right at the beginning of the function you can see that the function gets the new value of the parameter, but you can see that the value has not been updated in the cell on the sheet yet so beware. – Kyle Weller Aug 31 '13 at 00:52

score 3 · Answer 2 · answered Aug 29 '13 at 09:51

3

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$3") <> 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.

answered Aug 29 '13 at 09:51

Diego

31
1

I thought this was going to work, but it does not. I watch my cell D3 change and no event triggered. – Kyle Weller Aug 29 '13 at 20:38
It should work only when you change B3 or C3 in your example. If you change D3 directly it shoudn't work. – Diego Sep 18 '13 at 10:30

score 1 · Answer 3 · answered May 13 '15 at 18:20

1

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"

answered May 13 '15 at 18:20

walter's human

103
1
1
9

score 1 · Answer 4 · answered Feb 21 '18 at 20:25

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.

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

VBA trigger macro on cell value change

4 Answers4

Linked