Need to break up my SheetSelectionChange routine
 

I have 72 sets of the code below (each one is different) for each week of my
6 week calendar, for a total of 432 sets. I now get a compile error
"Procedure to Large". So what I would like to do is create a module for each
week and then refer to the module in the SheetSelectionChange routine. Is
this possible and if so how do I edit the code below so that it will work in
that situation?



If Range("C4") = "" Then
If Not Intersect(Target, Range("B5:C48")) Is Nothing Then
MsgBox "You have selected a day that is not available for
vacation. Please reselect."
Range("A3").Select
End
End If
End If

Need to break up my SheetSelectionChange routine
 

How are they different? It may possible to condense your code dramatically
depending on what the actual difference are. Can you give us a sample of 5
or 6 **consecutive** sets so we can see if there is a pattern to your sets?

--
Rick (MVP - Excel)



"ordnance1" wrote in message
...
I have 72 sets of the code below (each one is different) for each week of
my 6 week calendar, for a total of 432 sets. I now get a compile error
"Procedure to Large". So what I would like to do is create a module for
each week and then refer to the module in the SheetSelectionChange
routine. Is this possible and if so how do I edit the code below so that
it will work in that situation?



If Range("C4") = "" Then
If Not Intersect(Target, Range("B5:C48")) Is Nothing Then
MsgBox "You have selected a day that is not available for
vacation. Please reselect."
Range("A3").Select
End
End If
End If

Need to break up my SheetSelectionChange routine
 

'Sunday Day

'Office

If Range("Q7") = 0 Then
If Not Intersect(Target, Range("B5:C9")) Is Nothing Then
MsgBox "You have selected a day that is not
available for vacation. Please reselect."
Range("A3").Select
End
End If
End If

If Range("Q7") = 1 Then
If Not Intersect(Target, Range("B6:C9")) Is Nothing Then
MsgBox "You have selected a day that is not
available for vacation. Please reselect."
Range("A3").Select
End
End If
End If

If Range("Q7") = 2 Then
If Not Intersect(Target, Range("B7:C9")) Is Nothing Then
MsgBox "You have selected a day that is not
available for vacation. Please reselect."
Range("A3").Select
End
End If
End If

If Range("Q7") = 3 Then
If Not Intersect(Target, Range("B8:C9")) Is Nothing Then
MsgBox "You have selected a day that is not
available for vacation. Please reselect."
Range("A3").Select
End
End If
End If

If Range("Q7") = 4 Then
If Not Intersect(Target, Range("B9:C9")) Is Nothing Then
MsgBox "You have selected a day that is not
available for vacation. Please reselect."
Range("A3").Select
End
End If
End If


"Rick Rothstein" wrote in message
...
How are they different? It may possible to condense your code dramatically
depending on what the actual difference are. Can you give us a sample of 5
or 6 **consecutive** sets so we can see if there is a pattern to your
sets?

--
Rick (MVP - Excel)



"ordnance1" wrote in message
...
I have 72 sets of the code below (each one is different) for each week of
my 6 week calendar, for a total of 432 sets. I now get a compile error
"Procedure to Large". So what I would like to do is create a module for
each week and then refer to the module in the SheetSelectionChange
routine. Is this possible and if so how do I edit the code below so that
it will work in that situation?



If Range("C4") = "" Then
If Not Intersect(Target, Range("B5:C48")) Is Nothing Then
MsgBox "You have selected a day that is not available for
vacation. Please reselect."
Range("A3").Select
End
End If
End If

Need to break up my SheetSelectionChange routine
 

Based on the pattern I see so far, **all** of your code for Range("Q7")
values greater than or equal to 0 and less than or equal to 4 (maybe even
less than or equal to 5 depending on whether is a valid value for Q7 to
contain) can be replaced with this single code set...

If Not Intersect(target, Range(Range("B5").Offset( _
Range("Q7")), Range("C9"))) Is Nothing Then
MsgBox "You have selected a day that is not " & _
"available for vacation. Please reselect."
Range("A3").Select
Exit Sub
End If

The rest of your sets can probably be condensed as well, either individually
or perhaps into a single modification of the above set, but that is hard to
say without seeing the rest of your sets and what patterns they contain. Can
you copy/paste all 72 of your sets (or is it 432... I'm still not quite sure
of your layout) for this SheetSelectionChange event procedure into a
response to this message so we can see what else can be done?

--
Rick (MVP - Excel)



"ordnance1" wrote in message
...
'Sunday Day

'Office

If Range("Q7") = 0 Then
If Not Intersect(Target, Range("B5:C9")) Is Nothing
Then
MsgBox "You have selected a day that is not
available for vacation. Please reselect."
Range("A3").Select
End
End If
End If

If Range("Q7") = 1 Then
If Not Intersect(Target, Range("B6:C9")) Is Nothing
Then
MsgBox "You have selected a day that is not
available for vacation. Please reselect."
Range("A3").Select
End
End If
End If

If Range("Q7") = 2 Then
If Not Intersect(Target, Range("B7:C9")) Is Nothing
Then
MsgBox "You have selected a day that is not
available for vacation. Please reselect."
Range("A3").Select
End
End If
End If

If Range("Q7") = 3 Then
If Not Intersect(Target, Range("B8:C9")) Is Nothing
Then
MsgBox "You have selected a day that is not
available for vacation. Please reselect."
Range("A3").Select
End
End If
End If

If Range("Q7") = 4 Then
If Not Intersect(Target, Range("B9:C9")) Is Nothing
Then
MsgBox "You have selected a day that is not
available for vacation. Please reselect."
Range("A3").Select
End
End If
End If


"Rick Rothstein" wrote in message
...
How are they different? It may possible to condense your code
dramatically depending on what the actual difference are. Can you give us
a sample of 5 or 6 **consecutive** sets so we can see if there is a
pattern to your sets?

--
Rick (MVP - Excel)



"ordnance1" wrote in message
...
I have 72 sets of the code below (each one is different) for each week
of my 6 week calendar, for a total of 432 sets. I now get a compile
error "Procedure to Large". So what I would like to do is create a
module for each week and then refer to the module in the
SheetSelectionChange routine. Is this possible and if so how do I edit
the code below so that it will work in that situation?



If Range("C4") = "" Then
If Not Intersect(Target, Range("B5:C48")) Is Nothing Then
MsgBox "You have selected a day that is not available for
vacation. Please reselect."
Range("A3").Select
End
End If
End If