[SOLVED] Count Cell Changes

Count Cell Changes

Current script tracks cell changes from cell A1 and counts those changes in cell B1. However it only works when you actively input a number or text and then hit enter in cell A1. I need the formula to work when the A1 cell is referenced from another cell to count the changes.

Option Explicit
Private prevVal As String
Private Sub ToggleButton1_Click()

End Sub

Private Sub Worksheet_Calculate()
  If Worksheets("Sheet1").ToggleButton1.Value = True Then
  Application.EnableEvents = False
    If Me.Range("A1").Value <> prevVal Then
    Me.Range("B1").Value = Me.Range("B1").Value + 1
    prevVal = Me.Range("A1").Value
    End If
 End If
 Application.EnableEvents = True
End Sub

Solution

If the "A1" cell value is the result of a formula, you should use Calculate event. Please, copy this code event in the sheet code module:

Option Explicit

Private prevVal As String
Private Sub Worksheet_Calculate()
   Application.EnableEvents = False
   If Me.Range("A1").value <> prevVal Then
        Me.Range("B1").value = Me.Range("B1").value + 1
        prevVal = Me.Range("A1").value
    End If
   Application.EnableEvents = True
End Sub