VBA not working!

[jsweeney147]

Hi, I am relatively new to VBA so looking for some help as to why my code is not working properly. I am a teacher and basically trying to create a document that will make my life a little easier. I've got a dropdown list in column C and column G for each pupil, and the option chosen here creates a dependent drop down list in columns D and H. I'm trying to write some code that will automatically delete the contents of columns D and H if options are changed in columns C or G. This code works fine for the first pupil in my list but when I change an option for other pupils, it then deletes the contents of column D and H for all pupils. Can anyone help me? It's a looooonnnggg bit of code so far so I'm trying desperately to make it work.

Dim Pupil1 As Range, Pupil2 As Range, Pupil3 As Range, Pupil_1 As Range, Pupil_2 As Range, Pupil_3 As Range, Pupil4 As Range, Pupil_4 As Range, Pupil5 As Range, Pupil_5 As Range, Pupil6 As Range, Pupil_6 As Range, Pupil7 As Range, Pupil_7 As Range, Pupil8 As Range, Pupil_8 As Range, Pupil9 As Range, Pupil_9 As Range, Pupil10 As Range, Pupil_10 As Range
Dim Pupil11 As Range, Pupil_11 As Range, Pupil12 As Range, Pupil_12 As Range, Pupil13 As Range, Pupil_13 As Range, Pupil14 As Range, Pupil_14 As Range, Pupil15 As Range, Pupil_15 As Range, Pupil16 As Range, Pupil_16 As Range, Pupil17 As Range, Pupil_17 As Range, Pupil18 As Range, Pupil_18 As Range, Pupil19 As Range, Pupil_19 As Range, Pupil20 As Range, Pupil_20 As Range
Dim Pupil21 As Range, Pupil_21 As Range, Pupil22 As Range, Pupil_22 As Range, Pupil23 As Range, Pupil_23 As Range, Pupil24 As Range, Pupil_24 As Range, Pupil25 As Range, Pupil_25 As Range, Pupil26 As Range, Pupil_26 As Range, Pupil27 As Range, Pupil_27 As Range, Pupil28 As Range, Pupil_28 As Range, Pupil29 As Range, Pupil_29 As Range, Pupil30 As Range, Pupil_30 As Range
Dim Pupil31 As Range, Pupil_31 As Range, Pupil32 As Range, Pupil_32 As Range, Pupil33 As Range, Pupil_33 As Range, Pupil34 As Range, Pupil_34 As Range, Pupil35 As Range, Pupil_35 As Range, Pupil36 As Range, Pupil_36 As Range, Pupil37 As Range, Pupil_37 As Range, Pupil38 As Range, Pupil_38 As Range, Pupil39 As Range, Pupil_39 As Range, Pupil40 As Range, Pupil_40 As Range
Dim Pupil41 As Range, Pupil_41 As Range, Pupil42 As Range, Pupil_42 As Range, Pupil43 As Range, Pupil_43 As Range, Pupil44 As Range, Pupil_44 As Range, Pupil45 As Range, Pupil_45 As Range, Pupil46 As Range, Pupil_46 As Range, Pupil47 As Range, Pupil_47 As Range, Pupil48 As Range, Pupil_48 As Range, Pupil49 As Range, Pupil_49 As Range, Pupil50 As Range, Pupil_50 As Range
Dim Pupil51 As Range, Pupil_51 As Range, Pupil52 As Range, Pupil_52 As Range, Pupil53 As Range, Pupil_53 As Range, Pupil54 As Range, Pupil_54 As Range, Pupil55 As Range, Pupil_55 As Range, Pupil56 As Range, Pupil_56 As Range, Pupil57 As Range, Pupil_57 As Range, Pupil58 As Range, Pupil_58 As Range, Pupil59 As Range, Pupil_59 As Range, Pupil60 As Range, Pupil_60 As Range

Set Pupil1 = Range("D8:D11")
Set Pupil_1 = Range("C8")
Set Pupil2 = Range("H8:H11")
Set Pupil_2 = Range("G8")
Set Pupil3 = Range("D12:D15")
Set Pupil_3 = Range("C12")
Set Pupil4 = Range("H12:H15")
Set Pupil_4 = Range("G12")
Set Pupil5 = Range("D16:D19")
Set Pupil_5 = Range("C16")
Set Pupil6 = Range("H16:H19")
Set Pupil_6 = Range("G16")
Set Pupil7 = Range("D20:D23")
Set Pupil_7 = Range("C20")
Set Pupil8 = Range("H20:H23")
Set Pupil_8 = Range("G20")
Set Pupil9 = Range("D24:D27")
Set Pupil_9 = Range("C23")
Set Pupil10 = Range("H24:H27")
Set Pupil_10 = Range("G24")
Set Pupil11 = Range("D28:D31")
Set Pupil_11 = Range("C28")
Set Pupil12 = Range("H28:H31")
Set Pupil_12 = Range("G28")
Set Pupil13 = Range("D32:D35")
Set Pupil_13 = Range("C32")
Set Pupil14 = Range("H32:H35")
Set Pupil_14 = Range("G32")
Set Pupil15 = Range("D36:D39")
Set Pupil_15 = Range("C36")
Set Pupil16 = Range("H36:H39")
Set Pupil_16 = Range("G36")
Set Pupil17 = Range("D40:D43")
Set Pupil_17 = Range("C40")
Set Pupil18 = Range("H40:H43")
Set Pupil_18 = Range("G40")
Set Pupil19 = Range("D44:D47")
Set Pupil_19 = Range("C44")
Set Pupil20 = Range("H44:H47")
Set Pupil_20 = Range("G44")
Set Pupil21 = Range("D48:D51")
Set Pupil_21 = Range("C48")
Set Pupil22 = Range("H48:H51")
Set Pupil_22 = Range("G48")
Set Pupil23 = Range("D52:D55")
Set Pupil_23 = Range("C52")
Set Pupil24 = Range("H52:H55")
Set Pupil_24 = Range("G52")
Set Pupil25 = Range("D56:D59")
Set Pupil_25 = Range("C56")
Set Pupil26 = Range("H56:H59")
Set Pupil_26 = Range("G56")
Set Pupil27 = Range("D60:D63")
Set Pupil_27 = Range("C60")
Set Pupil28 = Range("H60:H63")
Set Pupil_28 = Range("G60")
Set Pupil29 = Range("D64:D67")
Set Pupil_29 = Range("C64")
Set Pupil30 = Range("H64:H67")
Set Pupil_30 = Range("G64")
Set Pupil31 = Range("D68:D71")
Set Pupil_31 = Range("C68")
Set Pupil32 = Range("H68:H71")
Set Pupil_32 = Range("G68")
Set Pupil33 = Range("D72:D75")
Set Pupil_33 = Range("C72")
Set Pupil34 = Range("H72:H75")
Set Pupil_34 = Range("G72")
Set Pupil35 = Range("D76:D79")
Set Pupil_35 = Range("C76")
Set Pupil36 = Range("H76:H79")
Set Pupil_36 = Range("G76")
Set Pupil37 = Range("D80:D83")
Set Pupil_37 = Range("C80")
Set Pupil38 = Range("H80:H83")
Set Pupil_38 = Range("G80")
Set Pupil39 = Range("D84:D87")
Set Pupil_39 = Range("C84")
Set Pupil40 = Range("H84:H87")
Set Pupil_40 = Range("G84")
Set Pupil41 = Range("D88:D91")
Set Pupil_41 = Range("C88")
Set Pupil42 = Range("H88:H91")
Set Pupil_42 = Range("G88")
Set Pupil43 = Range("D92:D95")
Set Pupil_43 = Range("C92")
Set Pupil44 = Range("H92:H95")
Set Pupil_44 = Range("G92")
Set Pupil45 = Range("D96:D99")
Set Pupil_45 = Range("C96")
Set Pupil46 = Range("H96:H99")
Set Pupil_46 = Range("G96")
Set Pupil47 = Range("D100:D103")
Set Pupil_47 = Range("C100")
Set Pupil48 = Range("H100:H103")
Set Pupil_48 = Range("G100")
Set Pupil49 = Range("D104:D107")
Set Pupil_49 = Range("C104")
Set Pupil50 = Range("H104:H107")
Set Pupil_50 = Range("G104")
Set Pupil51 = Range("D108:D111")
Set Pupil_51 = Range("C108")
Set Pupil52 = Range("H108:H111")
Set Pupil_52 = Range("G108")
Set Pupil53 = Range("D112:D115")
Set Pupil_53 = Range("C112")
Set Pupil54 = Range("H112:H115")
Set Pupil_54 = Range("G112")
Set Pupil55 = Range("D116:D119")
Set Pupil_55 = Range("C116")
Set Pupil56 = Range("H116:H119")
Set Pupil_56 = Range("G116")
Set Pupil57 = Range("D120:D123")
Set Pupil_57 = Range("C120")
Set Pupil58 = Range("H120:H123")
Set Pupil_58 = Range("G120")
Set Pupil59 = Range("D124:D127")
Set Pupil_59 = Range("C124")
Set Pupil60 = Range("H124:H127")
Set Pupil_60 = Range("G124")


If Target = Pupil_1 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil1.ClearContents
End If
End If

If Target = Pupil_2 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil2.ClearContents
End If
End If

If Target = Pupil_3 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil3.ClearContents
End If
End If

If Target = Pupil_4 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil4.ClearContents
End If
End If

If Target = Pupil_5 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil5.ClearContents
End If
End If

If Target = Pupil_6 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil6.ClearContents
End If
End If

If Target = Pupil_7 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil7.ClearContents
End If
End If

If Target = Pupil_8 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil8.ClearContents
End If
End If

If Target = Pupil_9 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil9.ClearContents
End If
End If

If Target = Pupil_10 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil10.ClearContents
End If
End If

If Target = Pupil_11 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil11.ClearContents
End If
End If

If Target = Pupil_12 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil12.ClearContents
End If
End If

If Target = Pupil_13 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil13.ClearContents
End If
End If

If Target = Pupil_14 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil14.ClearContents
End If
End If

If Target = Pupil_15 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil15.ClearContents
End If
End If

If Target = Pupil_16 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil16.ClearContents
End If
End If

If Target = Pupil_17 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil17.ClearContents
End If
End If

If Target = Pupil_18 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil18.ClearContents
End If
End If

If Target = Pupil_19 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil19.ClearContents
End If
End If

If Target = Pupil_20 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil20.ClearContents
End If
End If

If Target = Pupil_21 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil21.ClearContents
End If
End If

If Target = Pupil_22 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil22.ClearContents
End If
End If

If Target = Pupil_23 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil23.ClearContents
End If
End If

If Target = Pupil_24 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil24.ClearContents
End If
End If

If Target = Pupil_25 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil25.ClearContents
End If
End If

If Target = Pupil_26 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil26.ClearContents
End If
End If

If Target = Pupil_27 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil27.ClearContents
End If
End If

If Target = Pupil_28 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil28.ClearContents
End If
End If

If Target = Pupil_29 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil29.ClearContents
End If
End If

If Target = Pupil_30 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil30.ClearContents
End If
End If

If Target = Pupil_31 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil31.ClearContents
End If
End If

If Target = Pupil_32 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil32.ClearContents
End If
End If

If Target = Pupil_33 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil33.ClearContents
End If
End If

If Target = Pupil_34 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil34.ClearContents
End If
End If

If Target = Pupil_35 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil35.ClearContents
End If
End If

If Target = Pupil_36 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil36.ClearContents
End If
End If

If Target = Pupil_37 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil37.ClearContents
End If
End If

If Target = Pupil_38 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil38.ClearContents
End If
End If

If Target = Pupil_39 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil39.ClearContents
End If
End If

If Target = Pupil_40 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil40.ClearContents
End If
End If

If Target = Pupil_41 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil41.ClearContents
End If
End If

If Target = Pupil_42 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil42.ClearContents
End If
End If

If Target = Pupil_43 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil43.ClearContents
End If
End If

If Target = Pupil_44 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil44.ClearContents
End If
End If

If Target = Pupil_45 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil45.ClearContents
End If
End If

If Target = Pupil_46 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil46.ClearContents
End If
End If

If Target = Pupil_47 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil47.ClearContents
End If
End If

If Target = Pupil_48 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil48.ClearContents
End If
End If

If Target = Pupil_49 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil49.ClearContents
End If
End If

If Target = Pupil_50 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil50.ClearContents
End If
End If

If Target = Pupil_51 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil51.ClearContents
End If
End If

If Target = Pupil_52 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil52.ClearContents
End If
End If

If Target = Pupil_53 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil53.ClearContents
End If
End If

If Target = Pupil_54 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil54.ClearContents
End If
End If

If Target = Pupil_55 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil55.ClearContents
End If
End If

If Target = Pupil_56 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil56.ClearContents
End If
End If

If Target = Pupil_57 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil57.ClearContents
End If
End If

If Target = Pupil_58 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil58.ClearContents
End If
End If

If Target = Pupil_59 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil59.ClearContents
End If
End If

If Target = Pupil_60 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Pupil60.ClearContents
End If
End If


On Error Resume Next

exitHandler:
  Application.EnableEvents = True
  Exit Sub

End Sub

 

[Engberg]

This should do what you describe, and I think it replaces all your current code. Place it in the worksheet-specific module. It assumes that C and G are merged cells 2 rows high, and that each pupil had 4 rows each in columns D and H.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
        If Target.Column = Columns("C").Column Or Target.Column = Columns("G").Column Then
            For i = 0 To 3
                Cells(Target.Row + i, Columns("D").Column).ClearContents
                Cells(Target.Row + i, Columns("H").Column).ClearContents
            Next
        End If
    Application.EnableEvents = True
End Sub

 

[jsweeney147]

Application.EnableEvents = False If Target.Column = Columns("C").Column Or Target.Column = Columns("G").Column Then For i = 0 To 3 Cells(Target.Row + i, Columns("D").Column).ClearContents Cells(Target.Row + i, Columns("H").Column).ClearContents Next End If Application.EnableEvents = True End Sub

You are a genius. Thank you so much. Works perfectly. To think I spent hours writing all of that code and it could be done in a few lines! Thanks again!