Worksheet change subs.

[Guest]

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

 

[Dave Peterson]

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

 

[Norman Jones]

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

Is it possible to trigger a Private sub worksheet change for one range and

not another in the same worksheet?

 

[Andrew B]

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
(e-mail address removed)

Is it possible to trigger a Private sub worksheet change for one range and

not another in the same worksheet?

 

[Guest]

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