View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
COM COM is offline
external usenet poster
 
Posts: 40
Default Many Sub Worksheet_Change(ByVal Target As Range) In One Worksheet

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..