How can I record data changes in a cell from a formulas?

Tracker doesn't do this function

Just a single or a few cells?

Excel won't help you by telling what cells changed values. You'll have to keep
track of them yourself--maybe in a hidden worksheet???

Then you could use the worksheet_calculate (or Workbook_SheetCalculate) event to
look at each of those cells. If the value changed from the previous value, then
write it to that hidden worksheet.

This checks a single cell (A1) of a worksheet. And the code goes in the
worksheet module for that sheet.

Option Explicit
Private Sub Worksheet_Calculate()
Dim LastCell As Range
With Worksheets("hidden")
Set LastCell = .Cells(.Rows.Count, "A").End(xlUp)
End With

If Me.Range("A1").Value2 = LastCell.Value2 Then
'no change
Else
Application.EnableEvents = False
LastCell.Offset(1, 0).Value2 = Me.Range("a1").Value2
Application.EnableEvents = True
End If
End Sub




sausages wrote:

How can I record data changes in a cell from a formulas?

Tracker doesn't do this function

--

Dave Peterson

There's a bug in that program.

Change this line:
With Worksheets("hidden")
to
With me.parent.Worksheets("hidden")

If the worksheet recalculates when its workbook is not active, then the original
code would blow up!


Dave Peterson wrote:

Just a single or a few cells?

Excel won't help you by telling what cells changed values. You'll have to keep
track of them yourself--maybe in a hidden worksheet???

Then you could use the worksheet_calculate (or Workbook_SheetCalculate) event to
look at each of those cells. If the value changed from the previous value, then
write it to that hidden worksheet.

This checks a single cell (A1) of a worksheet. And the code goes in the
worksheet module for that sheet.

Option Explicit
Private Sub Worksheet_Calculate()
Dim LastCell As Range
With Worksheets("hidden")
Set LastCell = .Cells(.Rows.Count, "A").End(xlUp)
End With

If Me.Range("A1").Value2 = LastCell.Value2 Then
'no change
Else
Application.EnableEvents = False
LastCell.Offset(1, 0).Value2 = Me.Range("a1").Value2
Application.EnableEvents = True
End If
End Sub

sausages wrote:

How can I record data changes in a cell from a formulas?

Tracker doesn't do this function

--

Dave Peterson

--

Dave Peterson