Cell Change triggers Worksheet Event

C

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.
 
B

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
 
P

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
 
R

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
 
B

Bob Phillips

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

HTH

Bob
 
C

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
Click to expand...

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.
 
R

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
 
R

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.
 
C

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
Click to expand...

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.
 
G

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
 
C

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
Click to expand...

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.
 
C

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.
Click to expand...

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.
 
R

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
 
C

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
Click to expand...

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Simple Copy Paste within Worksheet Change Event 2
Change_Event troubles, no error & no work. 6
Macro to Select text from a point given to a specific word 2
Copy filtered data to sheet 2 1
Printing issue 1
Code execution is correct but generates Error :The file you aretrying to open is in a different form 3
Object range failed 1
Macro help 0

Top