Worksheet change subs.
 

Is it possible to trigger a Private sub worksheet change for one range and not another in the same worksheet?
Erik

Worksheet change subs.
 

I don't think so. But you could just have your sub exit if the change isn't in
the range you want:

I start a lot of my worksheet_change procedures with lines like this:

If target.cells.count 1 then exit sub
if intersect(target,me.range("a3:b99")) is nothing then exit sub



Erik wrote:

Is it possible to trigger a Private sub worksheet change for one range and not another in the same worksheet?
Erik

--

Dave Peterson

Worksheet change subs.
 

Hi Erik,

Yes. To demonstrate, the following throws up a msgbox if any cells in the
range A1:C10 are changed and does nothing for any cells outside this range:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range

Set Rng = Me.Range("A1:C10")

If Target.Count 1 Then Exit Sub
If Not Intersect(Target, Rng) Is Nothing Then
'Do something. For example:
MsgBox Target.Address
End If

End Sub

---
Regards,
Norman
"Erik" wrote in message
...
Is it possible to trigger a Private sub worksheet change for one range and
not another in the same worksheet?
Erik

Worksheet change subs.
 

Yes.
In the private sub test for the required range and proceed if it is correct.
e.g.
Private Sub SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

If Target.Count 1 Then Exit Sub

If Not Intersect(Target, Range("C5:J5")) Is Nothing then ' C5:J5
represents the range you want.

....your routine

end if


end sub

HTH
Andrew Bourke



"Erik" wrote in message
...
Is it possible to trigger a Private sub worksheet change for one range and
not another in the same worksheet?
Erik

Worksheet change subs.
 

Thanks everyone. I had a little infinite loop problem, but your suggestions solved it. I really appreciate all your help.
Erik