Many Sub Worksheet_Change(ByVal Target As Range) In One Worksheet

  • Thread starter Thread starter MathewPBennett
  • Start date Start date
M

MathewPBennett

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
 
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 said:
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
Click to expand...
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
 
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
 
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..
 
Back
Top