Cell Change triggers Worksheet Event

[Charles]

HI,

I'm trying to trigger a worksheet event thru a cell or cells value
being changed.
I found this code in this forum and it does what I'm trying to do,
however, I'm trying to have the event activated when a cell value
change within a column. The range I'm looking at is Range("S2:S75").
The values in this range are derived by a formula. Here's the code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
On Error GoTo errExit
Application.EnableEvents = False
Set rng = Range("s7").Precedents

If Not Intersect(Target, rng) Is Nothing Then

If Range("s7").Value <> 0 Then
' do something
MsgBox Range("s7").Value
End If
End If

errExit:
Application.EnableEvents = True
End Sub

This code works, but it only looks at 1 cell, and I need it to look at
the range specified.

Any help or suggestion would be appreciated.

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
On Error GoTo errExit
Application.EnableEvents = False
If Not Intersect(Target, Range("S2:S75").) Is Nothing Then

If Target.Value <> 0 Then
' do something
MsgBoxTarget.Value
End If
End If

errExit:
Application.EnableEvents = True
End Sub

 

[Per Jessen]

Hi

This should do it:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
On Error GoTo errExit
Application.EnableEvents = False
Set rng = Range("S2:S75")

If Not Intersect(Target, rng) Is Nothing Then

If Target.Value <> 0 Then
' do something
MsgBox Target.Value
End If
End If

errExit:
Application.EnableEvents = True
End Sub

Regards,
Per

 

[Rick Rothstein]

If Not Intersect(Target, Range("S2:S75").) Is Nothing Then

You have the "dot" after the range, but you forgot the Precedents keyword
after it...

If Not Intersect(Target, Range("S2:S75").Precedents) Is Nothing Then

 

[Bob Phillips]

I deliberately dropped the Precedents, couldn't see the point of it.

HTH

Bob

 

[Charles]

You have the "dot" after the range, but you forgot the Precedents keyword
after it...

If Not Intersect(Target, Range("S2:S75").Precedents) Is Nothing Then

Thanks to all who replayed. However the solutions provided did not
work for what I was trying to accomplish.
The actual target is the cell value in column S.
I made a change in column Q and the event is triggered, however I need
it to look at the values in column S.
The values in column S will change when any cell value the formula
looks at changes.
This is the formula I have in column S

IFERROR(IF(J10="----","",IF(J10="Buy",IF(G10<H10,"Stopped","In
trade"),IF(J10="Sell",IF(F10>H10,"Stopped",""),"In trade"))),"")


I hope this helps.

 

[Ryan H]

Just so I understand what you are wanting. If a cell (the Target) is changed
and is linked to any of the cells in Range("S2:S75") you want to "do
something" to any cell within that range that doesn't equal 0 then show the
value of that cell, right? If so, this code should help. Hope this helps!
If so, let me know, click "YES" below.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim MyRange As Range
Dim rng As Range

Set MyRange = Range("S2:S75")

If Intersect(Target, MyRange.Precedents) Is Nothing Then
Exit Sub
End If

Application.EnableEvents = False

' scan each rng in MyRange
For Each rng In MyRange
If rng.Value <> 0 Then
' do something
MsgBox rng.Value
End If
Next rng

Application.EnableEvents = True

End Sub

 

[Rick Rothstein]

Then you left the "dot" in there by mistake.<g> As for whether the
Precedents property call is needed or not... it might be. What if, in the
most simplest form, you had this in A1...

=B1<>""

and in B1 you had this...

=C1<>""

If C1 changes, then A1 will change, but the Change event code will not be
triggered if all you check is A1 in the event... you would need to check
A1's Precedents to get the right trigger for the event. Since the OP said
his code for the single cell (which contained a formula) that he checked
the Precedents for worked, I assumed the Precedents property call would be
needed in the modified code as well.

 

[Charles]

Just so I understand what you are wanting.  If a cell (the Target) is changed
and is linked to any of the cells in Range("S2:S75") you want to "do
something" to any cell within that range that doesn't equal 0 then show the
value of that cell, right?  If so, this code should help.  Hope this helps!  
If so, let me know, click "YES" below.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim MyRange As Range
Dim rng As Range

    Set MyRange = Range("S2:S75")

    If Intersect(Target, MyRange.Precedents) Is Nothing Then
        Exit Sub
    End If

    Application.EnableEvents = False

    ' scan each rng in MyRange
    For Each rng In MyRange
        If rng.Value <> 0 Then
            ' do something
            MsgBox rng.Value
        End If
    Next rng

    Application.EnableEvents = True

End Sub

Ryan,

I'm looking for any value change in column S (Range "S2:S74"). If any
cell value in column S changes then it should trigger the Event.
The latest code you proved works, but can it not be done with out the
For/Next loop.

 

[Gord Dibben]

Since the values in S2:S75 are formula-derived I would say the Precedents is
required.

At least in my testing it is required.

S2 formula is =A2

Change A2 and receive MsgBoxTarget.Value


Gord

 

[Charles]

Since the values in S2:S75 are formula-derived I would say the Precedentsis
required.

At least in my testing it is required.

S2 formula is    =A2

Change A2 and receive  MsgBoxTarget.Value  

Gord

Ok,

I may have made an error in what I posted.
I Apologize for this error. and many thanks for all who responded
However, the cell value derived from the formula that I have in all
cell in Range("S2:S74") is based on streaming data that is in another
cell but same row as the formula.
I'm try to look for the change made in column S to trigger the event.
So, now I'm not sure if this can be done with out a manual change.

 

[Charles]

Ok,

I may have made an error in what I posted.
I Apologize for this error. and many thanks for all who responded
However, the cell value derived from the  formula that I have in all
cell in Range("S2:S74") is based on streaming data that is in another
cell but same row as the formula.
I'm try to look for the change made in column S to trigger the event.
So, now I'm not sure if this can be done with out a manual change.

Thought I'd pass this on.
Instead of using a worksheet change event, I should have used the
Worksheet Calculate event.
Still thanks to all who responded to my post.

 

[Ryan H]

Ok, I think I have a better understanding of what you want. You have the
following formula in all cells from S2:S74, right?

IFERROR(IF(J10="----","",IF(J10="Buy",IF(G10<H10,"Stopped","In
trade"),IF(J10="Sell",IF(F10>H10,"Stopped",""),"In trade"))),"")

Thus, Col.S is effected by Col. F, G, H, & J. For example, if F10 is
changed and S10 is not equal to 0 you want to "do something", right? If so,
this is the code that will work for you. Hope this helps! If so, let me
know, click "YES" below.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim MyRange As Range

' ranges that effect formula in Col.S
Set MyRange = Range("F2:F74,G2:G74,H2:H74,J2:J74")

' if cell that is changed is not in MyRange exit sub else continue code
If Intersect(Target, MyRange) Is Nothing Then
Exit Sub
End If

' disable events to avoid Change event from firing again
Application.EnableEvents = False

' if cell doesn't equal 0 do something and show value
If Cells(Target.Row, "S").Value <> 0 Then
' do something
MsgBox Cells(Target.Row, "S").Value
End If

' enable events again
Application.EnableEvents = True

End Sub

 

[Charles]

Ok, I think I have a better understanding of what you want.  You have the
following formula in all cells from S2:S74, right?

IFERROR(IF(J10="----","",IF(J10="Buy",IF(G10<H10,"Stopped","In
trade"),IF(J10="Sell",IF(F10>H10,"Stopped",""),"In trade"))),"")

Thus, Col.S is effected by Col. F, G, H, & J.  For example, if F10 is
changed and S10 is not equal to 0 you want to "do something", right?  If so,
this is the code that will work for you.  Hope this helps!  If so, let me
know, click "YES" below.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim MyRange As Range

    ' ranges that effect formula in Col.S
    Set MyRange = Range("F2:F74,G2:G74,H2:H74,J2:J74")

    ' if cell that is changed is not in MyRange exit sub else continue code
    If Intersect(Target, MyRange) Is Nothing Then
        Exit Sub
    End If

    ' disable events to avoid Change event from firing again
    Application.EnableEvents = False

    ' if cell doesn't equal 0 do something and show value
    If Cells(Target.Row, "S").Value <> 0 Then
        ' do something
        MsgBox Cells(Target.Row, "S").Value
    End If

    ' enable events again
    Application.EnableEvents = True

End Sub

Ryan,

Once again thanks for you help. I'll try you code tomorrow when the
Dow is open and see if when any value in the specified range change
which would result a change in the cell value for column S would
trigger the Event. However I'm now in the mind set that a physical
"data" change not an "Streaming" data change must take place.(The
streaming data will be in the cells of column F. I get the data in
column F via a link.