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