Multiple Worksheet_Change(ByVal Target As Range) In Single Worksheet

M

MathewPBennett

Good evening all.

I am learning the usefulness of the Sub Worksheet_Change(ByVal Target As Range)

However I cannot seem to resolve the fact that I do not seem to be allowed
to have more than one of these subroutines in the same worksheet, even though
they do different things and have different Target Values and/or Ranges.

Am I missing something fundamental here? I have tried naming the sub differently,
ie with Sub Worksheet2_Change(ByVal Target As Range) or
Sub Worksheet_Change2(ByVal Target As Range).. etc, but to no avail.

Is this possible, or do I have to combine all my requirements into one sub?

Any help (again) would be very welcom
Thank you
Mathew
 
R

Ron de Bruin

You can only use one Change event in a worksheet but
you can run it on different ranges

See example below
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("a:a"), Target) Is Nothing Then MsgBox "A column"
If Not Application.Intersect(Range("b:b"), Target) Is Nothing Then MsgBox "b column"
If Not Application.Intersect(Range("c:c"), Target) Is Nothing Then MsgBox "C column"
End Sub
 
Top