A different Change event

[frubeng]

Hi all,

I am looking to have some code executed when a cell (containing a logical IF statement) changes from 0 to 1.

Right now, using the basic change event handler, it doesnt get activated when the value changes (only when the cell content changes, which is not what i need).

Does anyone know how to do this?


I thought of having a program running to check if the value ahs changed, but that would require a constant;y running program...

Thanks very much!!
Frubeng

[DannyUk]

Try using

 Worksheet_Calculate

event

Also for 1 and 0 return you don't need a if statement e.g

=N(A1="yes")

Danny

[mdmackillop]

Change A4 from 0 to 5, no change
Change A4 to >5 triggers messagebox macro and resets monitored value

[vba]
Dim remember As Long

Private Sub Worksheet_Activate()
remember = Cells(4, 5)
End Sub
Private Sub Worksheet_Calculate()
If Cells(4, 5) <> remember Then
remember = Cells(4, 5)
MsgBox "Changed"
End If
End Sub
[/vba]

[mikerickson]

Since the cell holds a formula, it will change value only when one of its precedent cells is changed by the user. Another way to say that is "if the cell holding the formula is a dependent of Target." If all the precidents of your key cell (cell B2 in the example below) are on the same sheet as your key cell. This kind of Change event would work.

[VBA]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
If Application.Intersect(Target.Dependents, Range("B2")) Is Nothing Then
Rem do nothing
Else
On Error Goto 0
Rem your routine
End If
On Error GoTo 0
End Sub[/VBA]

[frubeng]

Thanks for all the help!
Wow, this does exactly what I need!
Is there a way to have a a range rather than just cells(4,5).

I tried modifying Cells to Range but I get the error msg:

Run-time error '91'
Object variable or With block variable not set

This is with the code

Dim remember As Range

Private Sub Worksheet_Activate()
remember = Range("E4:E6")
End Sub
Private Sub Worksheet_Calculate()
If Range("E4:E6") <> remember Then
remember = Range("E4:E6")
MsgBox "Changed"
End If
End Sub


And yes, I can use N() instead of IF()

Change A4 from 0 to 5, no change
Change A4 to >5 triggers messagebox macro and resets monitored value

[vba]
Dim remember As Long

Private Sub Worksheet_Activate()
remember = Cells(4, 5)
End Sub
Private Sub Worksheet_Calculate()
If Cells(4, 5) <> remember Then
remember = Cells(4, 5)
MsgBox "Changed"
End If
End Sub
[/vba]

[mdmackillop]

This will not work if Cels = 1 (a single cell)
[VBA]Option Explicit
Dim remember
Const Cels = 2

Private Sub Worksheet_Activate()
remember = Cells(4, 5).Resize(Cels)
End Sub
Private Sub Worksheet_Calculate()
Dim i As Long
For i = 1 To Cels
If Cells(4, 5).Resize(Cels)(i) <> remember(i, 1) Then
remember = Cells(4, 5).Resize(Cels)
MsgBox "Changed"
Exit For
End If
Next
End Sub
[/VBA]

[frubeng]

So i put it yor code, but it gave me a
Run-time error '13':
Type mismatch
and the debugger higlighted the line: If Cells(6, 4).Resize(Cels)(i) <> remember(i, j) Then

That happened even before I added the outer For loop.
Any ideas?
[VBA]Option Explicit
Dim remember
Const Cels = 2
Const outlier_types = 3

Private Sub Worksheet_Activate()
remember = Cells(6, 4).Resize(Cels)
End Sub
Private Sub Worksheet_Calculate()
Dim i As Long
For j = 1 To outlier_types
For i = 1 To Cels
If Cells(6, 4).Resize(Cels)(i) <> remember(i, j) Then
remember = Cells(6, 4).Resize(Cels)
MsgBox "Changed"
Exit For
End If
Next
Next
End Sub[/VBA]


Thanks!!

[mdmackillop]

Can you post your workbook so I can see what range you are monitoring?

[frubeng]

I'm basically checking if any outliers pop up, so if any of the outlier columns turn from 0 to 1.
If that happens I will want to add text to a file, but that will come later i guess.