Need to break up my SheetSelectionChange routine

  • Thread starter Thread starter ordnance1
  • Start date Start date
O

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
 
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?
 
'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
 
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?
 
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
 
Back
Top