Procedure too large error

[dsheard2015]

Hello,

I have a worksheet selection change event code which is basically the same code copied 21 times with the only difference being the row number. Each row on the worksheet is for a different student. I am getting a "procedure too large" error and need to shorten the codes.

The code below represents the code for only 1 of the 21 students. the $3 is abviously row 3 which is the row for student 1, $4 would be for student 2 and so forth. The _a_ is also for student 1, _b_ would be for student 2 and so forth.

Basically, how can this code be shortened so that it can then be copied for all 21 students and still be within the allowable size limits? Any help is greatly appreciated.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
If Target.Address = "$E$3" Then Call open_student_a_admin
If Target.Address = "$F$3" Then Call print_a_1_admin
If Target.Address = "$G$3" Then Call print_a_2_admin
If Target.Address = "$H$3" Then Call print_a_3_admin
If Target.Address = "$I$3" Then Call print_a_4_admin
If Target.Address = "$J$3" Then Call print_a_5_admin
If Target.Address = "$K$3" Then Call print_a_6_admin
If Target.Address = "$L$3" Then Call print_a_7_admin
If Target.Address = "$M$3" Then Call print_a_8_admin
If Target.Address = "$N$3" Then Call print_a_9_admin
If Target.Address = "$O$3" Then Call print_a_10_admin
If Target.Address = "$P$3" Then Call print_a_11_admin
If Target.Address = "$Q$3" Then Call print_a_12_admin
If Target.Address = "$R$3" Then Call print_a_13_admin
If Target.Address = "$S$3" Then Call print_a_14_admin
If Target.Address = "$T$3" Then Call print_a_15_admin
If Target.Address = "$U$3" Then Call print_a_16_admin
If Target.Address = "$V$3" Then Call print_a_17_admin
If Target.Address = "$W$3" Then Call print_a_18_admin
If Target.Address = "$X$3" Then Call print_a_19_admin
If Target.Address = "$Y$3" Then Call print_a_20_admin
If Target.Address = "$Z$3" Then Call print_a_21_admin
If Target.Address = "$AA$3" Then Call print_a_22_admin
If Target.Address = "$AB$3" Then Call print_a_23_admin
If Target.Address = "$AC$3" Then Call print_a_24_admin
If Target.Address = "$AD$3" Then Call print_a_25_admin
If Target.Address = "$AE$3" Then Call print_a_26_admin
If Target.Address = "$AF$3" Then Call print_a_27_admin
If Target.Address = "$AG$3" Then Call print_a_28_admin
If Target.Address = "$AH$3" Then Call print_a_29_admin
If Target.Address = "$AI$3" Then Call print_a_30_admin
If Target.Address = "$AJ$3" Then Call print_a_31_admin
If Target.Address = "$AK$3" Then Call print_a_32_admin
If Target.Address = "$AL$3" Then Call print_a_33_admin
If Target.Address = "$AM$3" Then Call print_a_34_admin
If Target.Address = "$AN$3" Then Call print_a_35_admin
If Target.Address = "$AO$3" Then Call print_a_36_admin
If Target.Address = "$AP$3" Then Call print_a_37_admin
If Target.Address = "$AQ$3" Then Call print_a_38_admin
If Target.Address = "$AR$3" Then Call print_a_39_admin
If Target.Address = "$AS$3" Then Call print_a_40_admin
If Target.Address = "$AT$3" Then Call print_a_41_admin
If Target.Address = "$AU$3" Then Call print_a_42_admin
If Target.Address = "$AV$3" Then Call print_a_43_admin
If Target.Address = "$AW$3" Then Call print_a_44_admin
If Target.Address = "$AX$3" Then Call print_a_45_admin
If Target.Address = "$AY$3" Then Call print_a_46_admin
If Target.Address = "$AZ$3" Then Call print_a_47_admin
If Target.Address = "$BA$3" Then Call print_a_48_admin
If Target.Address = "$BB$3" Then Call print_a_49_admin
If Target.Address = "$BC$3" Then Call print_a_50_admin
If Target.Address = "$BD$3" Then Call print_a_51_admin
If Target.Address = "$BE$3" Then Call print_a_52_admin
If Target.Address = "$BF$3" Then Call print_a_53_admin
If Target.Address = "$BG$3" Then Call print_a_54_admin
If Target.Address = "$BH$3" Then Call print_a_55_admin
If Target.Address = "$BI$3" Then Call print_a_56_admin
If Target.Address = "$BJ$3" Then Call print_a_57_admin
If Target.Address = "$BK$3" Then Call print_a_58_admin
If Target.Address = "$BL$3" Then Call print_a_59_admin
If Target.Address = "$BM$3" Then Call print_a_60_admin
If Target.Address = "$BN$3" Then Call print_a_61_admin
If Target.Address = "$BO$3" Then Call print_a_62_admin
If Target.Address = "$BP$3" Then Call print_a_63_admin
If Target.Address = "$BQ$3" Then Call print_a_64_admin
If Target.Address = "$BR$3" Then Call print_a_65_admin
If Target.Address = "$BS$3" Then Call print_a_66_admin
If Target.Address = "$BT$3" Then Call print_a_67_admin
If Target.Address = "$BU$3" Then Call print_a_68_admin
If Target.Address = "$BV$3" Then Call print_a_69_admin
If Target.Address = "$BW$3" Then Call print_a_70_admin
If Target.Address = "$BX$3" Then Call print_a_71_admin
If Target.Address = "$BY$3" Then Call print_a_72_admin
If Target.Address = "$BZ$3" Then Call print_a_73_admin
If Target.Address = "$CA$3" Then Call print_a_74_admin
If Target.Address = "$CB$3" Then Call print_a_75_admin
If Target.Address = "$CC$3" Then Call print_a_76_admin
If Target.Address = "$CD$3" Then Call print_a_77_admin
If Target.Address = "$CE$3" Then Call print_a_78_admin
If Target.Address = "$CF$3" Then Call print_a_79_admin
If Target.Address = "$CG$3" Then Call print_a_80_admin
If Target.Address = "$CH$3" Then Call print_a_81_admin
If Target.Address = "$CI$3" Then Call print_a_82_admin
If Target.Address = "$CJ$3" Then Call print_a_83_admin
If Target.Address = "$CK$3" Then Call print_a_84_admin
If Target.Address = "$CL$3" Then Call print_a_85_admin
If Target.Address = "$CM$3" Then Call print_a_86_admin
If Target.Address = "$CN$3" Then Call print_a_87_admin
If Target.Address = "$CO$3" Then Call print_a_88_admin
If Target.Address = "$CP$3" Then Call print_a_89_admin
If Target.Address = "$CQ$3" Then Call print_a_90_admin
If Target.Address = "$CR$3" Then Call print_a_91_admin
If Target.Address = "$CS$3" Then Call print_a_92_admin
If Target.Address = "$CT$3" Then Call print_a_93_admin
If Target.Address = "$CU$3" Then Call print_a_94_admin
If Target.Address = "$CV$3" Then Call print_a_95_admin
If Target.Address = "$CW$3" Then Call print_a_96_admin
If Target.Address = "$CX$3" Then Call print_a_97_admin
If Target.Address = "$CY$3" Then Call print_a_98_admin
If Target.Address = "$CZ$3" Then Call print_a_99_admin
If Target.Address = "$DA$3" Then Call print_a_100_admin
If Target.Address = "$DB$3" Then Call print_a_101_admin
If Target.Address = "$DC$3" Then Call print_a_102_admin
If Target.Address = "$DD$3" Then Call print_a_103_admin
If Target.Address = "$DE$3" Then Call close_student_a_admin

End Sub

 

[Fluff]

What are the macros you're calling?

 

[dsheard2015]

The macros being called print specific sheet ranges within other workbooks. The code worked perfectly well when I had only 15 students but I needed to add additional students.

 

[dsheard2015]

Fluff, the macros being called refer to specific ranges found on various worksheets in other workbooks which are printed when that particular cell is selected.

 

[mole999]

Private Sub Worksheet_SelectionChange(ByVal Target As Range) I think is where your problem lies, there is a limit to the amount of text you can have in a sub

 

[dsheard2015]

mole999, exactly. That is the reason why I need to shorten this code. I just don't know of how I can do that.

 

[Fluff]

One option is to use select case statements like

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Select Case Target.Address(0, 0)
      Case "E3":     Call open_student_a_admin
      Case "F3":     Call print_a_1_admin
      Case "G3":     Call print_a_2_admin
   End Select
End Sub

But depending on what those print_a_1_admin etc macros do, it may be possible to put them all into 1 macro & pass it the correct ranges

 

[Rick Rothstein]

This is untested, but I think you can replace all of the code you posted in your first message with this (you will have to test it to see if I am right or not, of course)...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Address = "$E$3" Then
    Call open_student_a_admin
  ElseIf Target.Address = "$DE$3" Then
    Call close_student_a_admin
  ElseIf Not Intersect(Target, Range("F3:DD3")) Is Nothing Then
    Application.Run "print_a_" & Target.Column - 5 & "_admin"
  End If
End Sub

 

[dsheard2015]

Fluff,

I don't understand how using the select case statements will make the code any smaller, it appears to me that it will still require the same 105 lines of code as the way I have the code currently. Am I missing something?

 

[dsheard2015]

Rick,

Thanks for your response. I like what you suggested, it looks like it may just do the trick. I am away from my office until tomorrow but I will plug it in and give a try when I get back. I will be sure to update.

Thanks again!