Spotting when a cell changes . . .

Topic

New Reply

I have the following simple piece of code which updates a cell when the one adjacent changes – ie it keeps track of when updates are made:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False ‘Turn events off, or the changes progoate across the worksheet !

If ActiveCell.Column = 2 Then ‘Only apply the behaviour when the cell which changes is in Column 2
If ActiveCell.Row > 6 Then Target.Offset(0, 1) = Int(Now()) ‘Protects rows 1 to 6 from this behaviour
End If

Application.EnableEvents = True ‘Turn events back on

End Sub

If any cell in Column 2 has its value changed, the corresonding cell in Column 3 has the date set to “now”.

This all works fine when type the new value, or (as is the normal case) the new value is entered by a macro.

For various reasons, I have had to use formulae in the cells which can change, and the code no longer picks up the changes.

So my question is this: how do I make Excel spot that a particular cell’s value has changed and, if it has, update the date in the cell to its right ?

Many thanks

Martin

Reply | Quote

Replies

The change event is triggered when the contents of the cell change, not the value. If the formula is not changed, but the value, then the contents of the cell have changed.

What you will have to do is to not worry about the formulas (whose cells do not change) but the cells which do change and affect the formula. You either need to check each of the explicit cells which the formulae are dependent on, or check for changes in EVERY cell and look to see if they are precedents for the formulas in column 2. This 2nd method may be problematic if the precedent cells are on another worksheet as well as getting making data entry very sluggish as every change will have to be checked…

Steve

Reply | Quote

Hmmm . . monitoring the antecedent cells will be really tough as the specific source is the result of a complex lookup, and varies. I suppose I might be able do this when I work out how vlookup works in VBA !

The values of the result cells do change and I’m still unsure why the event doesn’t pick that up ?

Reply | Quote

Just a thought: If there aren’t too many references in a formula you could use a formula to pickup the latest {newest} date of any of it’s references.

So if A100 has the formula =A1*A7
B100 would have the formula =Max(B1,B7)

I hope this helps or at least gives you some ideas.

Edit: Sorry the Max function should have referenced col. B.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Thank you.

I think I’m in a dead-end here – time to get innovative and think about it differently. Your idea gave me some more – I am going to re-write the whole funtionality !

Reply | Quote

Thank you.

I think I’m in a dead-end here – time to get innovative and think about it differently. Your idea gave me some more – I am going to re-write the whole funtionality !

You could run a macro to snapshot the monitored cells to a hidden sheet – then use the code in column C to compare cells in B with the hidden values .

Run the snapshot on opening the worksheet and have button to run the macro on demand.

Reply | Quote

The values of the result cells do change and I’m still unsure why the event doesn’t pick that up ?

As I mentioned, the change event triggers on the cell CONTENTS not the cell’s VALUE. The value may change, but that is irrelevant, the content of the cell (the formula) has not changed and thus to excel, that cell has NOT changed. The cell that has changed (and that needs to be examined for a change) is the cell whose content did change.

If you have a simple formula in the cell c1:
=A1

When A1 changes, the value in C1 changes, but the change is A1 and checking to see if C1 changed will not be noted since C1 did NOT change, it still has the formula =A1…

Other than checking the cells that C1 is dependent on, the other option for looking at the values is to keep a table of all the values of the cells you are interested in and then whenever any cells are changed in the workbook, check the contents of all the cells of interest versus the saved table of past values and if the values have changed update the table and mark it as changed…

Steve

Reply | Quote

Thanks for all the ideas !

Using a combination of them I have fixed the issue as follows FYI.

Instead of doing a complex lookup on the sheet where data is displayed – the problem being as clearly stated, the value changes but the formula doesn’t – I have put the lookup on the sheet which looked at by the vlookup command. Then VBA writes the results to the sheet where the data is displayed, and of course the “On change” macro spots it and writes the datestamp of when the change took place.

Cheers everyone.

Reply | Quote