Need to break up my SheetSelectionChange routine

[ordnance1]

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

 

[Rick Rothstein]

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?

 

[ordnance1]

'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]

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?

 

[Tim Williams]

Based only on the sample you posted:

'*************************
dim tmp
tmp = Range("Q7").Value

if tmp>=0 and tmp<=4 then
If Not Intersect(Target, Range("B" & (5+tmp) & ":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
End If
'************************

Your full setup is clearly more complex than that, but this might give you
some ideas.


Tim