Plase help me clean up my code
 

On a worksheet, I have a whole series of related commands that launc
when the user clicks in one of four ranges of cells. WHat thos
commands are is unimportant. Suffice to say each set of VBA code fo
each of the four ranges of cells is a long series of code.

Right now, I have all four sections in one large module. I want t
create a separate sub-routine for each of the four sections, but
don't know how to do it (all efforts result in error).

The catch is that all of the commands stem from the user clicking in
given cell. Thus the sub starts with:

________
Private Sub Worksheet_SelectionChange(ByVal target As Range)

[Long code regarding range 1 of cells]

[Long code regarding range 2 of cells]

[Long code regarding range 3 of cells]

[Long code regarding range 4 of cells]
End sub
____________



What I want is the following:


________
Private Sub Worksheet_SelectionChange(ByVal target As Range)

[If user selects one cell in the range A1:A50 then call sub1]


[If user selects one cell in the range B1:B50 then call sub2]


[If user selects one cell in the range C1:CA50 then call sub3]


[If user selects one cell in the range D1:D50 then call sub4]


End sub

____________


Suggestions??

--
Message posted from http://www.ExcelForum.com

Plase help me clean up my code
 

Jason ,

Try this

Private Sub Worksheet_SelectionChange(ByVal target As Range)

If Not Intersect (Target, Range("A1:A50")) Is Nothing Then
sub1
ElseIf Not Intersect (Target, Range("B1:B50")) Is Nothing Then
sub2
ElseIf Not Intersect (Target, Range("C1:C50")) Is Nothing Then
sub3
ElseIf Not Intersect (Target, Range("D1:D50")) Is Nothing Then
sub4
End If

End sub



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"jasonsweeney " wrote in
message ...
On a worksheet, I have a whole series of related commands that launch
when the user clicks in one of four ranges of cells. WHat those
commands are is unimportant. Suffice to say each set of VBA code for
each of the four ranges of cells is a long series of code.

Right now, I have all four sections in one large module. I want to
create a separate sub-routine for each of the four sections, but I
don't know how to do it (all efforts result in error).

The catch is that all of the commands stem from the user clicking in a
given cell. Thus the sub starts with:

________
Private Sub Worksheet_SelectionChange(ByVal target As Range)

[Long code regarding range 1 of cells]

[Long code regarding range 2 of cells]

[Long code regarding range 3 of cells]

[Long code regarding range 4 of cells]
End sub
____________



What I want is the following:


________
Private Sub Worksheet_SelectionChange(ByVal target As Range)

[If user selects one cell in the range A1:A50 then call sub1]


[If user selects one cell in the range B1:B50 then call sub2]


[If user selects one cell in the range C1:CA50 then call sub3]


[If user selects one cell in the range D1:D50 then call sub4]


End sub

____________


Suggestions???


---
Message posted from http://www.ExcelForum.com/