Procedure Too Large - Compile Error

**JustJerry** · 08-20-2017, 10:01 PM

Seeking help in how to create a module and be able to call this to get rid of the Compile Error in Excel VBA. I've not done a lot of VBA for Excel, mostly on a novice base in Access.

I'm trying to have students names input by Last Name/First Name in cells starting at B9/C9 (student 1) on down to B43/C43 (Student 35). IF a name is entered into these cells, it will assign that name to a hidden cell on another sheet.
Also, it will rename tabs related to the student. Each student has two grading tabs. To avoid duplicate sheet names, I designated each combination of student sheets as S11/S11C (Student 1), S22/S22C (Student 2) and so on.....

If they delete either the first name or last name, then the tabs revert back the default names as I previously mentioned.

A "Y" will also appear in Column D and the row for the student they are entering

Below is the coding I have, which worked great for a few students, but now that I have my 35 students setup, it no longer works!

If anyone can help me, I would SOOOO appreciate it. I have pasted just the first two students so you can see how the references change to cells, etc.

Private Sub Worksheet_Change(ByVal Target As Range)

'Student 1 Auto Assign sheet name and student name on report card - check list - Grading sheets
If Not Intersect(Target, Range("B9")) Is Nothing Then
    If Range("B9").Value = "" Then
        Range("B9").Value = 1
        Range("D9") = "N"
        Worksheets(6).Name = Worksheets(1).Range("C9") & Worksheets(1).Range("B9")  'Rename Student Report Card worksheet to Default Name
        Worksheets(7).Name = Worksheets(1).Range("C9") & Worksheets(1).Range("B9") & "C"    'Rename Student Checklist worksheet to default name
        Range("A70").Value = ""   'Remove Report Card Name & Checklist Name
        Worksheets(2).Range("A70") = ""      'Remove Vocab Name
        If Range("C9").Value = "S1" Then
            Range("D9") = "N"
            Worksheets(6).Name = Worksheets(1).Range("C9") & "1"  'Rename Student Report Card worksheet to Student name
            Worksheets(7).Name = Worksheets(1).Range("C9") & "1C"   'Rename Student Checklist worksheet to Student name
            Range("A70").Value = ""   'Remove Report Card Name & Checklist Name
            Worksheets(2).Range("A70") = ""      'Remove Vocab Name
        Else
            Range("D9") = "Y"
            Worksheets(6).Name = Worksheets(1).Range("B9") & " " & Worksheets(1).Range("C9")    'Rename Student Report Card worksheet to Student name
            Worksheets(7).Name = Worksheets(1).Range("B9") & " " & Worksheets(1).Range("C9") & " Check" 'Rename Student Checklist worksheet to Student name
            Range("A70").Value = Range("B9").Value & ", " & Range("C9").Value    'Assign Report Card Name & Checklist Name
            Worksheets(2).Range("A70") = Range("B9").Value & ", " & Range("C9").Value   'Assign Vocab Name
        End If
    End If
End If
  
If Not Intersect(Target, Range("C9")) Is Nothing Then
    If Range("C9").Value = "" Then
        Range("C9").Value = "S1"
        Range("D9") = "N"
        Worksheets(6).Name = Worksheets(1).Range("C9") & "1"  'Rename Student Report Card worksheet to Default Name
        Worksheets(7).Name = Worksheets(1).Range("C9") & "1C" 'Rename Student Checklist worksheet to default name
        Range("A70").Value = ""   'Remove Report Card Name & Checklist Name
        Worksheets(2).Range("A70") = ""      'Remove Vocab Name
    Else
        If Range("B9").Value = "1" Then
            Range("D9") = "N"
            Worksheets(6).Name = "S1" & Worksheets(1).Range("B9")  'Rename Student Report Card worksheet to Default Name
            Worksheets(7).Name = "S1" & Worksheets(1).Range("B9") & "C"    'Rename Student Checklist worksheet to default name
            Range("A70").Value = ""   'Remove Report Card Name & Checklist Name
            Worksheets(2).Range("A70") = ""      'Remove Vocab Name
        Else
            Worksheets(6).Name = Worksheets(1).Range("B9") & " " & Worksheets(1).Range("C9")    'Rename Student Report Card worksheet to Student name
            Worksheets(7).Name = Worksheets(1).Range("B9") & " " & Worksheets(1).Range("C9") & " Check" 'Rename Student Checklist worksheet to Student name
            Range("D9") = "Y"
            Range("A70").Value = Range("B9").Value & ", " & Range("C9").Value    'Assign Report Card Name & Checklist Name
            Worksheets(2).Range("A70") = Range("B9").Value & ", " & Range("C9").Value   'Assign Vocab Name
        End If
    End If
End If
    'Student 2 Auto Assign sheet name and student name on report card - check list - Grading sheets
If Not Intersect(Target, Range("B10")) Is Nothing Then
    If Range("B10").Value = "" Then
        Range("B10").Value = 2
        Range("D10") = "N"
        Worksheets(8).Name = Worksheets(1).Range("C10") & Worksheets(1).Range("B10")  'Rename Student Report Card worksheet to Default Name
        Worksheets(9).Name = Worksheets(1).Range("C10") & Worksheets(1).Range("B10") & "C"    'Rename Student Checklist worksheet to default name
        Range("A71").Value = ""   'Remove Report Card Name & Checklist Name
        Worksheets(2).Range("A71") = ""      'Remove Vocab Name
        If Range("C10").Value = "S2" Then
            Range("D10") = "N"
            Worksheets(8).Name = Worksheets(1).Range("C10") & "2"  'Rename Student Report Card worksheet to Student name
            Worksheets(9).Name = Worksheets(1).Range("C10") & "2C"   'Rename Student Checklist worksheet to Student name
            Range("A71").Value = ""   'Remove Report Card Name & Checklist Name
            Worksheets(2).Range("A71") = ""      'Remvoe Vocab Name
        Else
            Range("D10") = "Y"
            Worksheets(8).Name = Worksheets(1).Range("B10") & " " & Worksheets(1).Range("C10")    'Rename Student Report Card worksheet to Student name
            Worksheets(9).Name = Worksheets(1).Range("B10") & " " & Worksheets(1).Range("C10") & " Check" 'Rename Student Checklist worksheet to Student name
            Range("A71").Value = Range("B10").Value & ", " & Range("C10").Value    'Assign Report Card Name & Checklist Name
            Worksheets(2).Range("A71") = Range("B10").Value & ", " & Range("C10").Value   'Assign Vocab Name
        End If
    End If
End If
  
If Not Intersect(Target, Range("C10")) Is Nothing Then
    If Range("C10").Value = "" Then
        Range("C10").Value = "S2"
        Range("D10") = "N"
        Worksheets(8).Name = Worksheets(1).Range("C10") & "2"  'Rename Student Report Card worksheet to Default Name
        Worksheets(9).Name = Worksheets(1).Range("C10") & "2C" 'Rename Student Checklist worksheet to default name
        Range("A71").Value = ""   'Remove Report Card Name & Checklist Name
        Worksheets(2).Range("A71") = ""      'Remove Vocab Name
    Else
        If Range("B10").Value = "2" Then
            Range("D10") = "N"
            Worksheets(8).Name = "S2" & Worksheets(1).Range("B10")  'Rename Student Report Card worksheet to Default Name
            Worksheets(9).Name = "S2" & Worksheets(1).Range("B10") & "C"    'Rename Student Checklist worksheet to default name
            Range("A71").Value = ""   'Remove Report Card Name & Checklist Name
            Worksheets(2).Range("A71") = ""      'Remove Vocab Name
        Else
            Worksheets(8).Name = Worksheets(1).Range("B10") & " " & Worksheets(1).Range("C10")    'Rename Student Report Card worksheet to Student name
            Worksheets(9).Name = Worksheets(1).Range("B10") & " " & Worksheets(1).Range("C10") & " Check" 'Rename Student Checklist worksheet to Student name
            Range("D10") = "Y"
            Range("A71").Value = Range("B10").Value & ", " & Range("C10").Value    'Assign Report Card Name & Checklist Name
            Worksheets(2).Range("A71") = Range("B10").Value & ", " & Range("C10").Value   'Assign Vocab Name
        End If
    End If
End If

End Sub

**macropod** · 08-20-2017, 11:06 PM

Cross-posted at: https://www.mrexcel.com/forum/excel-...ile-error.html
Please read VBA Express' policy on Cross-Posting in item 3 of the rules: http://www.vbaexpress.com/forum/faq...._new_faq_item3

**mdmackillop** · 08-21-2017, 12:43 AM

I don't get a compile error and would not attempt to solve this without sample data to allow the routine to run.

**Bob Phillips** · 08-21-2017, 02:50 AM

Remove the repetition by using a helper procedure

Private Sub Worksheet_Change(ByVal Target As Range)
     
    If Not Intersect(Target, Me.Range("B9")) Is Nothing Or Not Intersect(Target, Me.Range("C9")) Is Nothing Then
    
        Call StudentAssign(Target, "B9", "C9") 'Student 1
    ElseIf Not Intersect(Target, Me.Range("B10")) Is Nothing Or Not Intersect(Target, Me.Range("C10")) Is Nothing Then
    
        Call StudentAssign(Target, "B10", "C10") 'Student 2
    'elseif
    
        'Etc
    End If
End Sub

Private Function StudentAssign(ByRef Target As Range, ByVal TargetCell As String, ByVal TargetCell2 As String)
'Student Auto Assign sheet name and student name on report card - check list - Grading sheets

     If Not Intersect(Target, Range(TargetCell)) Is Nothing Then
    
        If Target.Value = "" Then
        
            Target.Value = 2
            Target.Offset(0, 2).Value = "N"
            Worksheets(8).Name = Target.Offset(0, 1).Value & Target.Value  'Rename Student Report Card worksheet to Default Name
            Worksheets(9).Name = Target.Offset(0, 1).Value & Target.Value & "C" 'Rename Student Checklist worksheet to default name
            Range("A71").Value = "" 'Remove Report Card Name & Checklist Name
            Worksheets(2).Range("A71") = "" 'Remove Vocab Name
            
            If Target.Offset(0, 1).Value = "S2" Then
            
                Target.Offset(0, 2) = "N"
                Worksheets(8).Name = Target.Offset(0, 1).Value & "2"  'Rename Student Report Card worksheet to Student name
                Worksheets(9).Name = Target.Offset(0, 1).Value & "2C"  'Rename Student Checklist worksheet to Student name
                Range("A71").Value = "" 'Remove Report Card Name & Checklist Name
                Worksheets(2).Range("A71") = "" 'Remvoe Vocab Name
            Else
            
                Target.Offset(0, 2) = "Y"
                Worksheets(8).Name = Target.Value & " " & Target.Offset(0, 1).Value  'Rename Student Report Card worksheet to Student name
                Worksheets(9).Name = Target.Value & " " & Target.Offset(0, 1).Value & " Check"  'Rename Student Checklist worksheet to Student name
                Range("A71").Value = Target.Value & ", " & Target.Offset(0, 1).Value  'Assign Report Card Name & Checklist Name
                Worksheets(2).Range("A71") = Target.Value & ", " & Target.Offset(0, 1).Value 'Assign Vocab Name
            End If
        End If
    ElseIf Not Intersect(Target, Range(TargetCell2)) Is Nothing Then
    
        If Target.Value = "" Then
        
            Target.Value = "S2"
             Target.Offset(0, 1).Value = "N"
            Worksheets(8).Name = Target.Value & "2" 'Rename Student Report Card worksheet to Default Name
            Worksheets(9).Name = Target.Value & "2C" 'Rename Student Checklist worksheet to default name
            Range("A71").Value = "" 'Remove Report Card Name & Checklist Name
            Worksheets(2).Range("A71") = "" 'Remove Vocab Name
        ElseIf Range("B10").Value = "2" Then
        
            Target.Offset(0, 1).Value = "N"
            Worksheets(8).Name = "S2" & Target.Offset(0, -1).Value 'Rename Student Report Card worksheet to Default Name
            Worksheets(9).Name = "S2" & Target.Offset(0, -1).Value & "C" 'Rename Student Checklist worksheet to default name
            Range("A71").Value = "" 'Remove Report Card Name & Checklist Name
            Worksheets(2).Range("A71") = "" 'Remove Vocab Name
        Else
        
            Worksheets(8).Name = Target.Offset(0, -1).Value & " " & Target.Value 'Rename Student Report Card worksheet to Student name
            Worksheets(9).Name = Target.Offset(0, -1).Value & " " & Target.Value & " Check" 'Rename Student Checklist worksheet to Student name
            Target.Offset(0, 1).Value = "Y"
            Range("A71").Value = Target.Offset(0, -1).Value & ", " & Target.Value 'Assign Report Card Name & Checklist Name
            Worksheets(2).Range("A71") = Target.Offset(0, -1).Value & ", " & Target.Value 'Assign Vocab Name
        End If
    End If
End Function

**JustJerry** · 08-21-2017, 08:46 PM

XLD!! Thank you. I tried out your code but it was only doing the first set of student tabs, and not the other 34 sets. However, after some rare thinking on my part, I was able to get this to work now. I had to add a Select statement to allow your code to know which student it was working on, and which tabs it should be changing. I can't thank you enough as I was worried I wouldn't get this done. My wife is a teacher, and I'm doing this grading/report card spreadsheet for her and ALL her other grade school teachers first through 4th grade. This is an upgraded version from the version my wife and I did last year. Again, Thank you so much.

Jerry

This is the code I came up with....and if you see anything obvious I should learn from, I would love to know.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Me.Range("B9")) Is Nothing Or Not Intersect(Target, Me.Range("C9")) Is Nothing Then
         
        Call StudentAssign(Target, "B9", "C9") 'Student 1
    ElseIf Not Intersect(Target, Me.Range("B10")) Is Nothing Or Not Intersect(Target, Me.Range("C10")) Is Nothing Then
         
        Call StudentAssign(Target, "B10", "C10") 'Student 2
    ElseIf Not Intersect(Target, Me.Range("B11")) Is Nothing Or Not Intersect(Target, Me.Range("C11")) Is Nothing Then
         
        Call StudentAssign(Target, "B11", "C11") 'Student 3
    ElseIf Not Intersect(Target, Me.Range("B12")) Is Nothing Or Not Intersect(Target, Me.Range("C12")) Is Nothing Then
         
        Call StudentAssign(Target, "B12", "C12") 'Student 4
    ElseIf Not Intersect(Target, Me.Range("B13")) Is Nothing Or Not Intersect(Target, Me.Range("C13")) Is Nothing Then
         
        Call StudentAssign(Target, "B13", "C13") 'Student 5
    ElseIf Not Intersect(Target, Me.Range("B14")) Is Nothing Or Not Intersect(Target, Me.Range("C14")) Is Nothing Then
         
        Call StudentAssign(Target, "B14", "C14") 'Student 6
    ElseIf Not Intersect(Target, Me.Range("B15")) Is Nothing Or Not Intersect(Target, Me.Range("C15")) Is Nothing Then
         
        Call StudentAssign(Target, "B15", "C15") 'Student 7
    ElseIf Not Intersect(Target, Me.Range("B16")) Is Nothing Or Not Intersect(Target, Me.Range("C16")) Is Nothing Then
         
        Call StudentAssign(Target, "B16", "C16") 'Student 8
    ElseIf Not Intersect(Target, Me.Range("B17")) Is Nothing Or Not Intersect(Target, Me.Range("C17")) Is Nothing Then
         
        Call StudentAssign(Target, "B17", "C17") 'Student 9
         'elseif
    ElseIf Not Intersect(Target, Me.Range("B18")) Is Nothing Or Not Intersect(Target, Me.Range("C18")) Is Nothing Then
         
        Call StudentAssign(Target, "B18", "C18") 'Student 10
    ElseIf Not Intersect(Target, Me.Range("B19")) Is Nothing Or Not Intersect(Target, Me.Range("C19")) Is Nothing Then
         
        Call StudentAssign(Target, "B19", "C19") 'Student 11
    ElseIf Not Intersect(Target, Me.Range("B20")) Is Nothing Or Not Intersect(Target, Me.Range("C20")) Is Nothing Then
         
        Call StudentAssign(Target, "B20", "C20") 'Student 12
    ElseIf Not Intersect(Target, Me.Range("B21")) Is Nothing Or Not Intersect(Target, Me.Range("C21")) Is Nothing Then
         
        Call StudentAssign(Target, "B21", "C21") 'Student 13
    ElseIf Not Intersect(Target, Me.Range("B22")) Is Nothing Or Not Intersect(Target, Me.Range("C22")) Is Nothing Then
         
        Call StudentAssign(Target, "B22", "C22") 'Student 14
    ElseIf Not Intersect(Target, Me.Range("B23")) Is Nothing Or Not Intersect(Target, Me.Range("C23")) Is Nothing Then
         
        Call StudentAssign(Target, "B23", "C23") 'Student 15
    ElseIf Not Intersect(Target, Me.Range("B24")) Is Nothing Or Not Intersect(Target, Me.Range("C24")) Is Nothing Then
         
        Call StudentAssign(Target, "B24", "C24") 'Student 16
    ElseIf Not Intersect(Target, Me.Range("B25")) Is Nothing Or Not Intersect(Target, Me.Range("C25")) Is Nothing Then
         
        Call StudentAssign(Target, "B25", "C25") 'Student 17
    ElseIf Not Intersect(Target, Me.Range("B26")) Is Nothing Or Not Intersect(Target, Me.Range("C26")) Is Nothing Then
         
        Call StudentAssign(Target, "B26", "C26") 'Student 18
    ElseIf Not Intersect(Target, Me.Range("B27")) Is Nothing Or Not Intersect(Target, Me.Range("C27")) Is Nothing Then
         
        Call StudentAssign(Target, "B27", "C27") 'Student 19
    ElseIf Not Intersect(Target, Me.Range("B28")) Is Nothing Or Not Intersect(Target, Me.Range("C28")) Is Nothing Then
         
        Call StudentAssign(Target, "B28", "C28") 'Student 20
    ElseIf Not Intersect(Target, Me.Range("B29")) Is Nothing Or Not Intersect(Target, Me.Range("C29")) Is Nothing Then
         
        Call StudentAssign(Target, "B29", "C29") 'Student 21
    ElseIf Not Intersect(Target, Me.Range("B30")) Is Nothing Or Not Intersect(Target, Me.Range("C30")) Is Nothing Then
         
        Call StudentAssign(Target, "B30", "C30") 'Student 22
    ElseIf Not Intersect(Target, Me.Range("B31")) Is Nothing Or Not Intersect(Target, Me.Range("C31")) Is Nothing Then
         
        Call StudentAssign(Target, "B31", "C31") 'Student 23
    ElseIf Not Intersect(Target, Me.Range("B32")) Is Nothing Or Not Intersect(Target, Me.Range("C32")) Is Nothing Then
         
        Call StudentAssign(Target, "B32", "C32") 'Student 24
    ElseIf Not Intersect(Target, Me.Range("B33")) Is Nothing Or Not Intersect(Target, Me.Range("C33")) Is Nothing Then
         
        Call StudentAssign(Target, "B33", "C33") 'Student 25
    ElseIf Not Intersect(Target, Me.Range("B34")) Is Nothing Or Not Intersect(Target, Me.Range("C34")) Is Nothing Then
         
        Call StudentAssign(Target, "B34", "C34") 'Student 26
    ElseIf Not Intersect(Target, Me.Range("B35")) Is Nothing Or Not Intersect(Target, Me.Range("C35")) Is Nothing Then
         
        Call StudentAssign(Target, "B35", "C35") 'Student 27
    ElseIf Not Intersect(Target, Me.Range("B36")) Is Nothing Or Not Intersect(Target, Me.Range("C36")) Is Nothing Then
         
        Call StudentAssign(Target, "B36", "C36") 'Student 28
    ElseIf Not Intersect(Target, Me.Range("B37")) Is Nothing Or Not Intersect(Target, Me.Range("C37")) Is Nothing Then
         
        Call StudentAssign(Target, "B37", "C37") 'Student 29
    ElseIf Not Intersect(Target, Me.Range("B38")) Is Nothing Or Not Intersect(Target, Me.Range("C38")) Is Nothing Then
         
        Call StudentAssign(Target, "B38", "C38") 'Student 30
    ElseIf Not Intersect(Target, Me.Range("B39")) Is Nothing Or Not Intersect(Target, Me.Range("C39")) Is Nothing Then
         
        Call StudentAssign(Target, "B39", "C39") 'Student 31
    ElseIf Not Intersect(Target, Me.Range("B40")) Is Nothing Or Not Intersect(Target, Me.Range("C40")) Is Nothing Then
         
        Call StudentAssign(Target, "B40", "C40") 'Student 32
    ElseIf Not Intersect(Target, Me.Range("B41")) Is Nothing Or Not Intersect(Target, Me.Range("C41")) Is Nothing Then
         
        Call StudentAssign(Target, "B41", "C41") 'Student 33
    ElseIf Not Intersect(Target, Me.Range("B42")) Is Nothing Or Not Intersect(Target, Me.Range("C42")) Is Nothing Then
         
        Call StudentAssign(Target, "B42", "C42") 'Student 34
    ElseIf Not Intersect(Target, Me.Range("B43")) Is Nothing Or Not Intersect(Target, Me.Range("C43")) Is Nothing Then
         
        Call StudentAssign(Target, "B43", "C43") 'Student 35
    
         'Etc
    End If
    
End Sub
Private Function StudentAssign(ByRef Target As Range, ByVal TargetCell As String, ByVal TargetCell2 As String)
     'Student Auto Assign sheet name and student name on report card - check list - Grading sheets
     
    Dim intws1 As Integer
    Dim intsw2 As Integer
    Dim intBcell As Integer
    Dim strCcell As String
    Dim strAcell As String
     
    If Not Intersect(Target, Range(TargetCell)) Is Nothing Then
    
        Select Case (TargetCell)
            Case "B9"
                intws1 = 6
                intws2 = 7
                intBcell = 1
                strCcell = "S1"
                strAcell = "A70"
            Case "B10"
                intws1 = 8
                intws2 = 9
                intBcell = 2
                strCcell = "S2"
                strAcell = "A71"
            Case "B11"
                intws1 = 10
                intws2 = 11
                intBcell = 3
                strCcell = "S3"
                strAcell = "A72"
            Case "B12"
                intws1 = 12
                intws2 = 13
                intBcell = 4
                strCcell = "S4"
                strAcell = "A73"
            Case "B13"
                intws1 = 14
                intws2 = 15
                intBcell = 5
                strCcell = "S5"
                strAcell = "A74"
            Case "B14"
                intws1 = 16
                intws2 = 17
                intBcell = 6
                strCcell = "S6"
                strAcell = "A75"
            Case "B15"
                intws1 = 18
                intws2 = 19
                intBcell = 7
                strCcell = "S7"
                strAcell = "A76"
            Case "B16"
                intws1 = 20
                intws2 = 21
                intBcell = 8
                strCcell = "S8"
                strAcell = "A77"
            Case "B17"
                intws1 = 22
                intws2 = 23
                intBcell = 9
                strCcell = "S9"
                strAcell = "A78"
            Case "B18"
                intws1 = 24
                intws2 = 25
                intBcell = 10
                strCcell = "S10"
                strAcell = "A79"
            Case "B19"
                intws1 = 26
                intws2 = 27
                intBcell = 11
                strCcell = "S11"
                strAcell = "A80"
            Case "B20"
                intws1 = 28
                intws2 = 29
                intBcell = 12
                strCcell = "S12"
                strAcell = "A81"
            Case "B21"
                intws1 = 30
                intws2 = 31
                intBcell = 13
                strCcell = "S13"
                strAcell = "A82"
            Case "B22"
                intws1 = 32
                intws2 = 33
                intBcell = 14
                strCcell = "S14"
                strAcell = "A83"
            Case "B23"
                intws1 = 34
                intws2 = 35
                intBcell = 15
                strCcell = "S15"
                strAcell = "A84"
            Case "B24"
                intws1 = 36
                intws2 = 37
                intBcell = 16
                strCcell = "S16"
                strAcell = "A85"
            Case "B25"
                intws1 = 38
                intws2 = 39
                intBcell = 17
                strCcell = "S17"
                strAcell = "A86"
            Case "B26"
                intws1 = 40
                intws2 = 41
                intBcell = 18
                strCcell = "S18"
                strAcell = "A87"
            Case "B27"
                intws1 = 42
                intws2 = 43
                intBcell = 19
                strCcell = "S19"
                strAcell = "A88"
            Case "B28"
                intws1 = 44
                intws2 = 45
                intBcell = 20
                strCcell = "S20"
                strAcell = "A89"
            Case "B29"
                intws1 = 46
                intws2 = 47
                intBcell = 21
                strCcell = "S21"
                strAcell = "A90"
            Case "B30"
                intws1 = 48
                intws2 = 49
                intBcell = 22
                strCcell = "S22"
                strAcell = "A91"
            Case "B31"
                intws1 = 50
                intws2 = 51
                intBcell = 23
                strCcell = "S23"
                strAcell = "A92"
            Case "B32"
                intws1 = 52
                intws2 = 53
                intBcell = 24
                strCcell = "S24"
                strAcell = "A93"
            Case "B33"
                intws1 = 54
                intws2 = 55
                intBcell = 25
                strCcell = "S25"
                strAcell = "A94"
            Case "B34"
                intws1 = 56
                intws2 = 57
                intBcell = 26
                strCcell = "S26"
                strAcell = "A95"
              Case "B35"
                intws1 = 58
                intws2 = 59
                intBcell = 27
                strCcell = "S27"
                strAcell = "A96"
            Case "B36"
                intws1 = 60
                intws2 = 61
                intBcell = 28
                strCcell = "S28"
                strAcell = "A97"
            Case "B37"
                intws1 = 62
                intws2 = 63
                intBcell = 29
                strCcell = "S29"
                strAcell = "A98"
            Case "B38"
                intws1 = 64
                intws2 = 65
                intBcell = 30
                strCcell = "S30"
                strAcell = "A99"
           Case "B39"
                intws1 = 66
                intws2 = 67
                intBcell = 31
                strCcell = "S31"
                strAcell = "A100"
            Case "B40"
                intws1 = 68
                intws2 = 69
                intBcell = 32
                strCcell = "S32"
                strAcell = "A101"
            Case "B41"
                intws1 = 70
                intws2 = 71
                intBcell = 33
                strCcell = "S33"
                strAcell = "A102"
            Case "B42"
                intws1 = 72
                intws2 = 73
                intBcell = 34
                strCcell = "S34"
                strAcell = "A103"
            Case "B43"
                intws1 = 74
                intws2 = 75
                intBcell = 35
                strCcell = "S35"
                strAcell = "A104"
        End Select
         
        If Target.Value = "" Then
             
            Target.Value = intBcell
            Target.Offset(0, 2).Value = "N"
            Worksheets(intws1).Name = Target.Offset(0, 1).Value & Target.Value 'Rename Student Report Card worksheet to Default Name
            Worksheets(intws2).Name = Target.Offset(0, 1).Value & Target.Value & "C" 'Rename Student Checklist worksheet to default name
            Range(strAcell).Value = "" 'Remove Report Card Name & Checklist Name
            Worksheets(2).Range(strAcell) = "" 'Remove Vocab Name
             
            If Target.Offset(0, 1).Value = strCcell Then
                 
                Target.Offset(0, 2) = "N"
                Worksheets(intws1).Name = Target.Offset(0, 1).Value & intBcell 'Rename Student Report Card worksheet to Student name
                Worksheets(intws2).Name = Target.Offset(0, 1).Value & strCcell 'Rename Student Checklist worksheet to Student name
                Range(strAcell).Value = "" 'Remove Report Card Name & Checklist Name
                Worksheets(2).Range(strAcell) = "" 'Remvoe Vocab Name
            Else
                 
                Target.Offset(0, 2) = "Y"
                Worksheets(intws1).Name = Target.Value & " " & Target.Offset(0, 1).Value 'Rename Student Report Card worksheet to Student name
                Worksheets(intws2).Name = Target.Value & " " & Target.Offset(0, 1).Value & " Check" 'Rename Student Checklist worksheet to Student name
                Range(strAcell).Value = Target.Value & ", " & Target.Offset(0, 1).Value 'Assign Report Card Name & Checklist Name
                Worksheets(2).Range(strAcell) = Target.Value & ", " & Target.Offset(0, 1).Value 'Assign Vocab Name
            End If
        End If
    ElseIf Not Intersect(Target, Range(TargetCell2)) Is Nothing Then
         
         Select Case (TargetCell2)
            Case "C9"
                intws1 = 6
                intws2 = 7
                intBcell = 1
                strCcell = "S1"
                strAcell = "A70"
            Case "C10"
                intws1 = 8
                intws2 = 9
                intBcell = 2
                strCcell = "S2"
                strAcell = "A71"
            Case "C11"
                intws1 = 10
                intws2 = 11
                intBcell = 3
                strCcell = "S3"
                strAcell = "A72"
            Case "C12"
                intws1 = 12
                intws2 = 13
                intBcell = 4
                strCcell = "S4"
                strAcell = "A73"
            Case "C13"
                intws1 = 14
                intws2 = 15
                intBcell = 5
                strCcell = "S5"
                strAcell = "A74"
            Case "C14"
                intws1 = 16
                intws2 = 17
                intBcell = 6
                strCcell = "S6"
                strAcell = "A75"
            Case "C15"
                intws1 = 18
                intws2 = 19
                intBcell = 7
                strCcell = "S7"
                strAcell = "A76"
            Case "C16"
                intws1 = 20
                intws2 = 21
                intBcell = 8
                strCcell = "S8"
                strAcell = "A77"
            Case "C17"
                intws1 = 22
                intws2 = 23
                intBcell = 9
                strCcell = "S9"
                strAcell = "A78"
            Case "C18"
                intws1 = 24
                intws2 = 25
                intBcell = 10
                strCcell = "S10"
                strAcell = "A79"
            Case "C19"
                intws1 = 26
                intws2 = 27
                intBcell = 11
                strCcell = "S11"
                strAcell = "A80"
            Case "C20"
                intws1 = 28
                intws2 = 29
                intBcell = 12
                strCcell = "S12"
                strAcell = "A81"
            Case "C21"
                intws1 = 30
                intws2 = 31
                intBcell = 13
                strCcell = "S13"
                strAcell = "A82"
            Case "C22"
                intws1 = 32
                intws2 = 33
                intBcell = 14
                strCcell = "S14"
                strAcell = "A83"
            Case "C23"
                intws1 = 34
                intws2 = 35
                intBcell = 15
                strCcell = "S15"
                strAcell = "A84"
            Case "C24"
                intws1 = 36
                intws2 = 37
                intBcell = 16
                strCcell = "S16"
                strAcell = "A85"
            Case "C25"
                intws1 = 38
                intws2 = 39
                intBcell = 17
                strCcell = "S17"
                strAcell = "A86"
            Case "C26"
                intws1 = 40
                intws2 = 41
                intBcell = 18
                strCcell = "S18"
                strAcell = "A87"
            Case "C27"
                intws1 = 42
                intws2 = 43
                intBcell = 19
                strCcell = "S19"
                strAcell = "A88"
            Case "C28"
                intws1 = 44
                intws2 = 45
                intBcell = 20
                strCcell = "S20"
                strAcell = "A89"
            Case "C29"
                intws1 = 46
                intws2 = 47
                intBcell = 21
                strCcell = "S21"
                strAcell = "A90"
            Case "C30"
                intws1 = 48
                intws2 = 49
                intBcell = 22
                strCcell = "S22"
                strAcell = "A91"
            Case "C31"
                intws1 = 50
                intws2 = 51
                intBcell = 23
                strCcell = "S23"
                strAcell = "A92"
            Case "C32"
                intws1 = 52
                intws2 = 53
                intBcell = 24
                strCcell = "S24"
                strAcell = "A93"
            Case "C33"
                intws1 = 54
                intws2 = 55
                intBcell = 25
                strCcell = "S25"
                strAcell = "A94"
            Case "C34"
                intws1 = 56
                intws2 = 57
                intBcell = 26
                strCcell = "S26"
                strAcell = "A95"
            Case "C35"
                intws1 = 58
                intws2 = 59
                intBcell = 27
                strCcell = "S27"
                strAcell = "A96"
            Case "C36"
                intws1 = 60
                intws2 = 61
                intBcell = 28
                strCcell = "S28"
                strAcell = "A97"
            Case "C37"
                intws1 = 62
                intws2 = 63
                intBcell = 29
                strCcell = "S29"
                strAcell = "A98"
            Case "C38"
                intws1 = 64
                intws2 = 65
                intBcell = 30
                strCcell = "S30"
                strAcell = "A99"
            Case "C39"
                intws1 = 66
                intws2 = 67
                intBcell = 31
                strCcell = "S31"
                strAcell = "A100"
            Case "C40"
                intws1 = 68
                intws2 = 69
                intBcell = 32
                strCcell = "S32"
                strAcell = "A101"
            Case "C41"
                intws1 = 70
                intws2 = 71
                intBcell = 33
                strCcell = "S33"
                strAcell = "A102"
            Case "C42"
                intws1 = 72
                intws2 = 73
                intBcell = 34
                strCcell = "S34"
                strAcell = "A103"
            Case "C43"
                intws1 = 74
                intws2 = 75
                intBcell = 35
                strCcell = "S35"
                strAcell = "A104"
        End Select
        
        If Target.Value = "" Then
             
            Target.Value = strCcell
            Target.Offset(0, 1).Value = "N"
            Worksheets(intws1).Name = Target.Value & intBcell 'Rename Student Report Card worksheet to Default Name
            Worksheets(intws2).Name = Target.Value & intBcell & "C" 'Rename Student Checklist worksheet to default name
            Range(strAcell).Value = "" 'Remove Report Card Name & Checklist Name
            Worksheets(2).Range(strAcell) = "" 'Remove Vocab Name
        ElseIf Range(TargetCell).Value = intBcell Then
             
            Target.Offset(0, 1).Value = "N"
            Worksheets(intws1).Name = strCcell & Target.Offset(0, -1).Value 'Rename Student Report Card worksheet to Default Name
            Worksheets(intws2).Name = strCcell & Target.Offset(0, -1).Value & "C" 'Rename Student Checklist worksheet to default name
            Range(strAcell).Value = "" 'Remove Report Card Name & Checklist Name
            Worksheets(2).Range(strAcell) = "" 'Remove Vocab Name
        Else
             
            Worksheets(intws1).Name = Target.Offset(0, -1).Value & " " & Target.Value 'Rename Student Report Card worksheet to Student name
            Worksheets(intws2).Name = Target.Offset(0, -1).Value & " " & Target.Value & " Check" 'Rename Student Checklist worksheet to Student name
            Target.Offset(0, 1).Value = "Y"
            Range(strAcell).Value = Target.Offset(0, -1).Value & ", " & Target.Value 'Assign Report Card Name & Checklist Name
            Worksheets(2).Range(strAcell) = Target.Offset(0, -1).Value & ", " & Target.Value 'Assign Vocab Name
        End If
    End If
End Function

**JustJerry** · 08-21-2017, 08:51 PM

mdmack....I think it had to do with the VBA file being over a certain size?? I have a bit of other code as well, so whatever it was, the idea XLD gave me worked.

**offthelip** · 08-22-2017, 01:42 AM

You should learn how to use loops in your code
Notice that your worksheet change code has the exactly the same code for B9 ,C9 and then B10 ,C10
A reference to B9 can be made numerically Cells(“B9”) is equivalent to Cells(9,2), where the 9 is the row number and 2 is the column number. ( for some silly reason excel vba reversed the order of columns and rows in the two reference systems. Or you can reference by use of code such as
Range("B" & i) where I is an index value.

So the whole of the worksheet change code could be changed to:

Private Sub Worksheet_Change(ByVal Target As Range)
    For i = 9 To 43
    If Not Intersect(Target, Range("B" & i)) Is Nothing Or Not Intersect(Target, Range("C" & i)) Is Nothing Then
            param1 = "B" & i
            param2 = "C" & i
            Call StudentAssign(Target, param1, param2) 'Student i
    End If
    Next i
    End Sub

looking further into your code all of the code in your studentassign function could be simplified because there is a clear pattern in the case statement, so you can get rid of the case statement entirely by passing the index value across to the function instead of the B9 ,C9 references so:

Case "B9"             intws1 = 6 
            intws2 = 7 
            intBcell = 1 
            strCcell = "S1" 
            strAcell = "A70"

etc , etc down to end case is replaced with

intws1=i-3
intws2=i-2
intBcell=i-8
strCcell=”S” & i-8
StrAcell= “A” & i+61

where "i" is the index from the worksheet change event

note:I have written code which is 10 times larger than yours without running into any size problems so that is not the reason it wouldn't compile in my opinion.

snb · 08-22-2017, 02:40 AM

Or ?

Private Sub Worksheet_Change(ByVal Target As Range) 
   If Not Intersect(Target, Range("B9:C43")) Is Nothing Then StudentAssign Target, "B" & target.row, "C" & target.row
End Sub

**offthelip** · 08-22-2017, 04:45 AM

you don't need to pass the parameters at all, because the"Target" has all the information needed so I reckon it can come doen to something like this ( note not tested)

Private Sub Worksheet_Change(ByVal Target As Range)
     
   
        If Not Intersect(Target, Range("B9:C43")) Is Nothing Then
            Call StudentAssign(Target) 'Student i
        End If
      
End Sub


Private Function StudentAssign(ByRef Target As Range)
     'Student Auto Assign sheet name and student name on report card - check list - Grading sheets
     
    Dim intws1 As Integer
    Dim intsw2 As Integer
    Dim intBcell As Integer
    Dim strCcell As String
    Dim strAcell As String
     
    If Not Intersect(Target, Range("B9:B43")) Is Nothing Then
        i = Target.Row
        intws1 = i - 3
        intws2 = i - 2
        intBcell = i - 8
        strCcell = ”S” & i - 8
        strAcell = “A” & i + 61
         
        If Target.Value = "" Then
             
            Target.Value = intBcell
            Target.Offset(0, 2).Value = "N"
            Worksheets(intws1).Name = Target.Offset(0, 1).Value & Target.Value 'Rename Student Report Card worksheet to Default Name
            Worksheets(intws2).Name = Target.Offset(0, 1).Value & Target.Value & "C" 'Rename Student Checklist worksheet to default name
            Range(strAcell).Value = "" 'Remove Report Card Name & Checklist Name
            Worksheets(2).Range(strAcell) = "" 'Remove Vocab Name
             
            If Target.Offset(0, 1).Value = strCcell Then
                 
                Target.Offset(0, 2) = "N"
                Worksheets(intws1).Name = Target.Offset(0, 1).Value & intBcell 'Rename Student Report Card worksheet to Student name
                Worksheets(intws2).Name = Target.Offset(0, 1).Value & strCcell 'Rename Student Checklist worksheet to Student name
                Range(strAcell).Value = "" 'Remove Report Card Name & Checklist Name
                Worksheets(2).Range(strAcell) = "" 'Remvoe Vocab Name
            Else
                 
                Target.Offset(0, 2) = "Y"
                Worksheets(intws1).Name = Target.Value & " " & Target.Offset(0, 1).Value 'Rename Student Report Card worksheet to Student name
                Worksheets(intws2).Name = Target.Value & " " & Target.Offset(0, 1).Value & " Check" 'Rename Student Checklist worksheet to Student name
                Range(strAcell).Value = Target.Value & ", " & Target.Offset(0, 1).Value 'Assign Report Card Name & Checklist Name
                Worksheets(2).Range(strAcell) = Target.Value & ", " & Target.Offset(0, 1).Value 'Assign Vocab Name
            End If
        End If
    Else
    If Not Intersect(Target, Range("C9:C43")) Is Nothing Then
    i = Target.Row
        intws1 = i - 3
        intws2 = i - 2
        intBcell = i - 8
        strCcell = ”S” & i - 8
        strAcell = “A” & i + 61
         


        If Target.Value = "" Then
             
            Target.Value = strCcell
            Target.Offset(0, 1).Value = "N"
            Worksheets(intws1).Name = Target.Value & intBcell 'Rename Student Report Card worksheet to Default Name
            Worksheets(intws2).Name = Target.Value & intBcell & "C" 'Rename Student Checklist worksheet to default name
            Range(strAcell).Value = "" 'Remove Report Card Name & Checklist Name
            Worksheets(2).Range(strAcell) = "" 'Remove Vocab Name
        ElseIf Range(TargetCell).Value = intBcell Then
             
            Target.Offset(0, 1).Value = "N"
            Worksheets(intws1).Name = strCcell & Target.Offset(0, -1).Value 'Rename Student Report Card worksheet to Default Name
            Worksheets(intws2).Name = strCcell & Target.Offset(0, -1).Value & "C" 'Rename Student Checklist worksheet to default name
            Range(strAcell).Value = "" 'Remove Report Card Name & Checklist Name
            Worksheets(2).Range(strAcell) = "" 'Remove Vocab Name
        Else
             
            Worksheets(intws1).Name = Target.Offset(0, -1).Value & " " & Target.Value 'Rename Student Report Card worksheet to Student name
            Worksheets(intws2).Name = Target.Offset(0, -1).Value & " " & Target.Value & " Check" 'Rename Student Checklist worksheet to Student name
            Target.Offset(0, 1).Value = "Y"
            Range(strAcell).Value = Target.Offset(0, -1).Value & ", " & Target.Value 'Assign Report Card Name & Checklist Name
            Worksheets(2).Range(strAcell) = Target.Offset(0, -1).Value & ", " & Target.Value 'Assign Vocab Name
        End If
    End If
End Function

**Aflatoon** · 08-22-2017, 05:00 AM

You don't really need this check in the StudentAssign function:

If Not Intersect(Target, Range("B9:B43")) Is Nothing Then

since it was already done in the Change event. I would recommend a loop through the cells that are in that intersection in case more than one cell is changed at a time.

**offthelip** · 08-22-2017, 05:10 AM

You don't really need this check in the StudentAssign function:

I disagree I think you need this check to tell whether the change is done on a cell in column B or column C because the output is different depending on which cell is changed.
I agree that if more than one cell is changed at once then it would be better to loop through them all.

**Aflatoon** · 08-22-2017, 06:24 AM

Apologies - I missed that you were using individual columns each time. It would be easier to just check the Column property though.

**offthelip** · 08-22-2017, 06:30 AM

It would be easier to just check the Column property though

You are quite right, it is all about how many changes from the original code it is worth making!!

**SamT** · 08-22-2017, 06:35 AM

You don't really need this check in the StudentAssign function:

If Target.Column = 2 then
'
'
'
ElseIf Target.Column = 3 then
'
'
'

The main issue I have with the workbook is that sheets in the Collection are indexed according to the Sheet Tab position, counting from left to right. This means that if for any reason a tab is moved, inserted, or deleted, the first time you run your code based on Sheets(Index#), the workbook will be "destroyed" and would be very difficult to rebuild

This issue can be solved by refactoring the workbook and code to use CodeNames, which are read only only be modified in the VBIDE by hand, but can be used in any code.

You do have to use a cludge to select a sheet with CodeNames

Sub t()
For Each Sht In Worksheets
If Sht.CodeName = "***" Then 
With Sht
.Cells(1, 1) = "AAA"
End with
Next
End Sub

IMO, it's worth it to protect a workbook like yours

Personally, I would prefill the workbook with enough sheets to handle the maximum allowed number of students and hide all unused sheets.

as S11/S11C (Student 1)

For i = NumStudents + 1 to MaxAllowedStudents
For each Sht in WorkSheets
If sht.Codename = "S" & i &  i Or sht.codename = "S" & i &  i  & "C" Then sht.visible = 2 '2 = veryhidden
Next
Next

.

**JustJerry** · 08-22-2017, 06:50 AM

I'm headed off to work this morning, just saw all these replies. Looks like I'll have some homework to do on this later! I'm always learning and do appreciate the lessons. Thanks again

Thread: Procedure Too Large - Compile Error

Thread Tools

Display

Procedure Too Large - Compile Error

Posting Permissions