Monitor cell for change

[wilg]

I'm putting all my code in the workbook object. Not the worksheets as I'm updating the code regulary and have 250 sheets in my workbook. So I don't want to update code 250 times..lol.

I need a piece of code that if cell "ak119" changes because of a formula changing its value it will msgbox me "hi" for eg.

I'm finding worksheet codes but I need to put this in the workbook object.

[mikerickson]

If you put this in the ThisWorkbook code module, it will tell you when the user changes the value of AK119 of any sheet in the workbook. The Union with the Precedents is to make sure the routine is triggered if AK119 conatins a formula. If the formula in AK119 has a formula that gets data from off the sheet, changing the offsheet value might change AK119, but not trigger this routine.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim keyRange As Range
Set keyRange = Sh.Range("AK119")
On Error Resume Next
Set keyRange = Application.Union(keyRange, keyRange.Precedents)
On Error GoTo 0
If Not Application.Intersect(keyRange, Target) Is Nothing Then
MsgBox "Cell AK119 of sheet " & Sh.Name & " has changed."
End If
End Sub
250 sheets, that's a lot! I'd get lost in that workbook.

[wilg]

Sorry maybe spoke too soon...checking.

[wilg]

It seems to rigger everytmie I key in a variable which is part of the formula. However the formula is not changing value.

The value I'm looking to trigger your code is everytime cell ak119 goes up by 1.

Maybe because my formula is checking my variable it triggers?

[mikerickson]

Excel's Change event is triggered even when the cell is "changed" from 10 to 10.

You'd have to store the 250 A119 values somewhere and check everytime to detect incrimenting by 1.

[macropod]

You'd have to store the 250 A119 values somewhere and check everytime to detect incrimenting by 1.
Alternatively, you could have the macro temporarily undo the change to get the old value, so it could be compared with the new one.

[mikerickson]

How would the change be caused?

If the SheetX!A119 is changed only by the user changing some cell value in SheetX, the Sheet Activate event could store the sheets A119 value for comparison.

[wilg]

The formula in ak119 is just =AK112+AK56

But everytime I key in any value that doesnt interfere with that formula it triggers your message.

[wilg]

is there no simple solution for this one..

I have a formula in ak119. If the value of the formula changes by 1, I need to trigger my other code.

I can only store this in the workbook object.

Thanks to all of you helping me on this one.