Good evening all.
Re-posted again from misc. - sorry again.(RDB)

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?

Thank you to Ron De Bruin for his post in misc. but I am really non-the-wiser.

my code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If Not Application.Intersect(Range("AJ:AK"), Target) Is Nothing Then
If Target.Value = "t" Then
Target.Value = Now
End If
End If
End Sub


I would like to repeat this in the same worksheet, for different ranges,
and for different target values then different date, ie next friday (for which
I have code from Tom O.)

Assistance required, sorry RDB.

Mathew




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.554 / Virus Database: 346 - Release Date: 20/12/2003

Mathew,

You can have only one Worksheet_Change event procedure in a
worksheet's code module, and this one procedure will be called
automatically when any cell on the sheet is changed. The
reference to the changed cell is passed to the procedure via the
Target parameter.

Perhaps you could clarify your question, and someone might post a
more specific answer.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"MathewPBennett" wrote in message
...
Good evening all.
Re-posted again from misc. - sorry again.(RDB)

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?

Thank you to Ron De Bruin for his post in misc. but I am really
non-the-wiser.

my code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If Not Application.Intersect(Range("AJ:AK"), Target) Is Nothing
Then
If Target.Value = "t" Then
Target.Value = Now
End If
End If
End Sub


I would like to repeat this in the same worksheet, for
different ranges,
and for different target values then different date, ie next
friday (for which
I have code from Tom O.)

Assistance required, sorry RDB.

Mathew




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.554 / Virus Database: 346 - Release Date:
20/12/2003

Hi Mathew!

It's just like Chip says, there can be only one sub of
that kind.
If you want to do different things depending on changes
in different areas of the worksheet, you can use the
passed range to check which cell is changed, and then
calling different sub's in a normal Module, depending on
that check. As something like this.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim dRow as Double
Dim iCol as Integer

Row = Target.Row
Col = Target.Column

If (Row 10 AND Row < 20) AND (Col 3 AND Col 10) Then
DoSomething
Else
DoSomethingElse
End If

Hope that help something
Zeth

End Sub



-----Original Message-----
Mathew,

You can have only one Worksheet_Change event procedure
in a
worksheet's code module, and this one procedure will be
called
automatically when any cell on the sheet is changed. The
reference to the changed cell is passed to the procedure
via the
Target parameter.

Perhaps you could clarify your question, and someone
might post a
more specific answer.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"MathewPBennett" wrote in message
...
Good evening all.
Re-posted again from misc. - sorry again.(RDB)

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?

Thank you to Ron De Bruin for his post in misc. but I
am really
non-the-wiser.

my code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If Not Application.Intersect(Range("AJ:AK"), Target)
Is Nothing
Then
If Target.Value = "t" Then
Target.Value = Now
End If
End If
End Sub


I would like to repeat this in the same worksheet, for
different ranges,
and for different target values then different date,
ie next
friday (for which
I have code from Tom O.)

Assistance required, sorry RDB.

Mathew




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system
(http://www.grisoft.com).
Version: 6.0.554 / Virus Database: 346 - Release Date:
20/12/2003


.

if target.address="$A$1" then do this
if target.address="$A$2" then do that
if target.address="$A$3" then do the other
--
Don Guillett
SalesAid Software

"MathewPBennett" wrote in message
...
Good evening all.
Re-posted again from misc. - sorry again.(RDB)

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?

Thank you to Ron De Bruin for his post in misc. but I am really
non-the-wiser.

my code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If Not Application.Intersect(Range("AJ:AK"), Target) Is Nothing Then
If Target.Value = "t" Then
Target.Value = Now
End If
End If
End Sub


I would like to repeat this in the same worksheet, for different ranges,
and for different target values then different date, ie next friday (for
which
I have code from Tom O.)

Assistance required, sorry RDB.

Mathew




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.554 / Virus Database: 346 - Release Date: 20/12/2003

I'm responding to the original message, but be sure to look at the other responses, as I will refer to them a little.

Yes, one copy of the sub-routine, though the sub-routine can handle many events, like the others have described.

In your situation you would like to do different things depending on what cell(s) is/are changed. I typically will pass the "target" range to another sub-routine to either have it do the work, or to at least sort things out. That way the code looks more readable.

For example

Public Sub Worksheet_Change(ByVal Target as Range)
'Things to do before the "work" is done
Call DoWork(Target)
'Other things to do after the "work" is done
End Sub

Private Sub DoWork(ByVal Target as Range)
if "in Range1" then
'do range1 stuff
else 'if wanting to else or could do an elseif
'do things not in Range1
end if

if "in Range2" then
'do Range2 stuff
elseif "in Range3" then
'do Range3 things that you didn't want done on Range2 things
end if

'etc
End Sub

I used "in RangeX" because others have answered with perfectly legitamate ways of filtering the data, mostly a matter of preference of how you would like to identify your range.

I think there was a post that used ="$A$1" you could also use something like "$A$1:$B$2" to specify a range of cells and there are several other ways that you can refer to the data passed by Target..