CODEWITHSUNDEEP
vbaexcel
marcigo36
marcigo36

Reputation: 525

Get notification for all cell changes in VBA

I want to get a notification for all cell updates in excel. My current code is something like this:

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

My problem is, that it only runs when a direct cell change happens. If I modify a cell, some other cells might change if their values relies on the modified cell. Is there a way to detect these changes too? I would like to avoid the mirror copy method.

Upvotes: 2

Views: 477

Answers (1)

Archimaredes
Archimaredes

Reputation: 1427

The following code will allow you to access all cells containing formulae which depend upon Target.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    ...
    On Error Resume Next
    For Each cell In Target.Dependents
        ' Do something
    Next cell
    On Error GoTo 0
    ...
End Sub

The On Error Resume Next statement is necessary because the loop will throw an error if there are no dependent cells.

You may also want to call Application.Calculate before that For Each loop in order to force re-calculation of those cells.

Upvotes: 3

Related Questions