Procedure Too Large - Compile Error

[JustJerry]

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]

Cross-posted at: https://www.mrexcel.com/forum/excel-questions/1019733-procedure-too-large-compile-error.html
Please read VBA Express' policy on Cross-Posting in item 3 of the rules: http://www.vbaexpress.com/forum/faq.php?faq=new_faq_item#faq_new_faq_item3

[mdmackillop]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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

[offthelip]

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]

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]

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