How do I fire an Excel VBA Macro whenever a cell value is updated?

0
votes

I have a Sub that I would like to run whenever cells are updated to contain a certain value.

Right now I'm using code like the following:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  If Target.Cells.Count = 1 Then
    If Target.Value = XYZ Then
      my_sub a, b, c
    End If
  End If
End Sub

The issue right now is that the macro only fires when I edit these cells directly, not when changes in other cells force these cells to change.

Additionally, these cells are not well defined, so I can not hard code "when A5 changes", for example. I need this to fire every time any cell in my workbook is updated (manually or through formulas) to meet my condition.

3
vbaexcel
What exactly isn't working here? Is it the fact that this doesn't capture when formulas change? In that case, look up the Worksheet.Calculate event (msdn.microsoft.com/en-us/library/office/ff838823.aspx). - Mikegrann
Also, just making sure that your code above isn't what you're actually using - if it is, do you instead want If target.value = "XYZ"? What's not working with the macro? - BruceWayne
It only fires when I edit the cell directly, not when changes in other cells force it to change. I'm not sure how to make it work with Worksheet.Calculate since there is no Target argument. And no, this sin't exactly the code, so the quotes on XYZ aren't an issue - user1923052
@user1923052 You're right, it doesn't have a target. There isn't really a built-in way to trigger code if a certain cell's formula causes it to change value. In the Calculate event you could search for cells containing XYZ, but that's expensive. Do you know which cells on the sheet (even just vaguely) will end up containing that value, or could it be any cell at all? - Mikegrann
For my specific purpose I actually can know exactly which cells will be updated. But I would like to put out the challenge (to myself and this board) to come up with a more general solution as it would makes this code significantly more reusable - user1923052

3 Answers

2
votes

Provided your target is only a single cell with a formula that needs to be monitored, this will work:

Option Explicit

Dim tarVal As Variant

Private Sub Worksheet_Activate()

    tarVal = ActiveSheet.Range("A1").Value ' change range parameter to the address of the target formula

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim tempVal As Variant

    tempVal = ActiveSheet.Range("A1").Value

    If tempVal <> tarVal Then
        tarVal = tempVal

        ' your code here

        MsgBox "The value of A1 has changed" ' for testing purposes only, delete later
    End If

End Sub

Edit

The following code works for an entire range of cells, but only if automatic calculation is turned on. In case the monitored cells are non-contiguous, just use union statements when defining the target range. (The target range is A1:A10 in this example). This is under the assumption that only one of formulas in the target range can change its value at a time. If multiple target formulas can do that, then remove Exit for in the Worksheet_Change subroutine.

Option Explicit

Dim tarCellCount As Long
Dim tarRng As Range
Dim tarVals As Variant

Private Sub Worksheet_Activate()

    Dim i As Long
    Dim cll As Range

    Set tarRng = ActiveSheet.Range("A1:A10") ' change range parameter to the addresses of the target formulas

    tarCellCount = tarRng.Cells.count
    ReDim tarVals(1 To tarCellCount) As Variant

    For Each cll In tarRng
        i = i + 1
        tarVals(i) = cll.Value
    Next cll

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim changeBool As Boolean
    Dim i As Long
    Dim cll As Range
    Dim tempVal As Variant

    For Each cll In tarRng
        tempVal = cll.Value
        i = i + 1

        If tempVal <> tarVals(i) Then
            tarVals(i) = tempVal
            changeBool = True
            Exit For
        End If
    Next cll

    If changeBool Then

      ' your code here

        MsgBox "The value of one of the cells in the target range has changed" ' for testing purposes only, delete later
    End If

End Sub
1
votes
  1. Add your cells to be tracked to a named formula (named range). I used rngValue
  2. Use a static variable to track how many times the value you want to track occurs in this range
  3. Use the Calculate event to check if the number of occurences changes

code

Private Sub Worksheet_Calculate()
Dim StrIn As String
Static lngCnt As Long
Dim lngCnt2 As Long

StrIn = "apples"

lngCnt2 = Application.WorksheetFunction.CountIf(Range("rngValue"), StrIn)
If lngCnt2 <> lngCnt Then
    lngCnt = lngCnt2
    Call mysub
End If

End Sub
-1
votes

Target is a range that CAN contain more cells. This code should work for you.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  For Each cell In Target.Cells
    If cell.Value = XYZ Then
      my_sub a, b, c
    End If
  Next cell
End Sub

Edit: I see that you want to fire that also when formula is updated defined value. It can be slow if you will check every cell, but really depends on the size of your file. Here is some code to give you idea how to do it.

Private Sub Workbook_SheetCalculate(ByVal sh As Object)
    For Each cell In  sh.Cells.SpecialCells(xlCellTypeFormulas).Cells
        If cell.Value = XYZ Then
           my_sub a, b, c
        End If
    Next cell
End Sub