Appendix Thread. ( Codes for other Threads, etc.) Event Coding Drpdown Data validation

[DocAElstein]

In support of this Thread post:
https://excelfox.com/forum/showthrea...ll=1#post15090


First attempt by siyab.

Private Sub Worksheet_Change(ByVal Target As Range)
Rem 0 worksheets info
Dim WsAdv As Worksheet, WsComs As Worksheet
 Set WsAdv = ThisWorkbook.Worksheets("Advise"): Set WsComs = ThisWorkbook.Worksheets("Comments")

Rem 1 Restrict  most of the macro workings to only be done when specific ranges are chosen. In our case those ranges are column A and C
    If Application.Intersect(Target, Me.Range("A2:A8,C2:C8")) Is Nothing Then
    ' do nothing because there was no intersection of the changed range, Target, and the cells of lists 1 and 2
    Else
    Rem 2
    Dim RwTrgt As Long: Let RwTrgt = Target.Row
     '2a_ --------------------------------------   Communicating effectively
        If Me.Range("A" & RwTrgt & "").Value = WsAdv.Range("A1").Value Then                 ' Communicating Effectively
        ' create list 4 for case Communicating Effectively
         Me.Range("E" & RwTrgt & "").Validation.Delete
         Me.Range("E" & RwTrgt & "").Validation.Add Type:=xlValidateList, Formula1:="=Advise!A2:A11"
        ' Now go through the 3 Choose Options for case Communicating Effectively
            If Me.Range("C" & RwTrgt & "").Value = WsComs.Range("A2").Value Then                 ' Does Not Meet Expectation
            '2a(i) create list 3 for case Communicating Effectively and Does Not Meet Expectation
             Me.Range("D" & RwTrgt & "").Validation.Delete
             Me.Range("D" & RwTrgt & "").Validation.Add Type:=xlValidateList, Formula1:="=Comments!A3:A8"
            ElseIf Me.Range("C" & RwTrgt & "").Value = WsComs.Range("B2").Value Then             ' Meets Expectation
            '2a(ii) create list 3 for case Communicating Effectively and Meets Expectation
             Me.Range("D" & RwTrgt & "").Validation.Delete
             Me.Range("D" & RwTrgt & "").Validation.Add Type:=xlValidateList, Formula1:="=Comments!B3:B8"
            
            ElseIf Me.Range("C" & RwTrgt & "").Value = WsComs.Range("C2").Value Then             ' Exceeds Expectation
            '2a(iii) create list 3 for case Communicating Effectively and Exceeds Expectation
             Me.Range("D" & RwTrgt & "").Validation.Delete
             Me.Range("D" & RwTrgt & "").Validation.Add Type:=xlValidateList, Formula1:="=Comments!C3:C8"

            End If ' this is end of cases of  {Does Not Meet Expectation,  Meets Expectation,  Exceeds Expectation} for the case of Communicating effectively for Social Competencies
    '_ --------------------------------------


    '2b_ --------------------------------------  Resolving Conflict
        ElseIf Me.Range("A" & RwTrgt & "").Value = WsAdv.Range("A14").Value Then             ' Resolving Conflict
        ' create list 4 for case Resolving Conflict
         Me.Range("E" & RwTrgt & "").Validation.Delete
         Me.Range("E" & RwTrgt & "").Validation.Add Type:=xlValidateList, Formula1:="=Advise!A15:A24"
        ' Now go through the 3 Choose Options for case Resolving Conflict
            If Me.Range("C" & RwTrgt & "").Value = WsComs.Range("A12").Value Then                 ' Does Not Meet Expectation
            '2b(i) create list 3 for case Communicating Effectively and Does Not Meet Expectation
             Me.Range("D" & RwTrgt & "").Validation.Delete
             Me.Range("D" & RwTrgt & "").Validation.Add Type:=xlValidateList, Formula1:="=Comments!A13:A18"
            ElseIf Me.Range("C" & RwTrgt & "").Value = WsComs.Range("B12").Value Then             ' Meets Expectation
            '2b(ii) create list 3 for case Communicating Effectively and Meets Expectation
             Me.Range("D" & RwTrgt & "").Validation.Delete
             Me.Range("D" & RwTrgt & "").Validation.Add Type:=xlValidateList, Formula1:="=Comments!B13:B18"
            
            ElseIf Me.Range("C" & RwTrgt & "").Value = WsComs.Range("C12").Value Then             ' Exceeds Expectation
            '2b(iii) create list 3 for case Communicating Effectively and Exceeds Expectation
             Me.Range("D" & RwTrgt & "").Validation.Delete
             Me.Range("D" & RwTrgt & "").Validation.Add Type:=xlValidateList, Formula1:="=Comments!C13:C18"

            End If ' this is end of cases of  {Does Not Meet Expectation,  Meets Expectation,  Exceeds Expectation} for the case of Resolving Conflicts for Social Competencies
    '_ --------------------------------------
        
        
    '2c_ --------------------------------------  Sharing Information
        
        ElseIf Me.Range("A" & RwTrgt & "").Value = WsAdv.Range("A27").Value Then             ' Sharing Information
        ' create list 4 for case Sharing Information
         Me.Range("E" & RwTrgt & "").Validation.Delete
         Me.Range("E" & RwTrgt & "").Validation.Add Type:=xlValidateList, Formula1:="=Advise!A28:A32"
        ' Now go through the 3 Choose Options for case Sharing Information
            If Me.Range("C" & RwTrgt & "").Value = WsComs.Range("A22").Value Then                 ' Does Not Meet Expectation
            '2c(i) create list 3 for case Communicating Effectively and Does Not Meet Expectation
             Me.Range("D" & RwTrgt & "").Validation.Delete
             Me.Range("D" & RwTrgt & "").Validation.Add Type:=xlValidateList, Formula1:="=Comments!A23:A28"
            ElseIf Me.Range("C" & RwTrgt & "").Value = WsComs.Range("B22").Value Then             ' Meets Expectation
            '2c(ii) create list 3 for case Communicating Effectively and Meets Expectation
             Me.Range("D" & RwTrgt & "").Validation.Delete
             Me.Range("D" & RwTrgt & "").Validation.Add Type:=xlValidateList, Formula1:="=Comments!B23:B28"
            
            ElseIf Me.Range("C" & RwTrgt & "").Value = WsComs.Range("C22").Value Then             ' Exceeds Expectation
            '2c(iii) create list 3 for case Communicating Effectively and Exceeds Expectation
             Me.Range("D" & RwTrgt & "").Validation.Delete
             Me.Range("D" & RwTrgt & "").Validation.Add Type:=xlValidateList, Formula1:="=Comments!C23:C28"

            End If ' this is end of cases of  {Does Not Meet Expectation,  Meets Expectation,  Exceeds Expectation} for the case of Sharing Information for Social Competencies
    '_ --------------------------------------
        
        
    '2d_ --------------------------------------  Supporting Co-workers
        
        ElseIf Me.Range("A" & RwTrgt & "").Value = WsAdv.Range("A35").Value Then             ' Supporting Co-workers
        ' create list 4 for case Supporting Co-workers
         Me.Range("E" & RwTrgt & "").Validation.Delete
         Me.Range("E" & RwTrgt & "").Validation.Add Type:=xlValidateList, Formula1:="=Advise!A36:A48"
        ' Now go through the 3 Choose Options for case Supporting Co-workers
            If Me.Range("C" & RwTrgt & "").Value = WsComs.Range("A32").Value Then                 ' Does Not Meet Expectation
            '2d(i) create list 3 for case Communicating Effectively and Does Not Meet Expectation
             Me.Range("D" & RwTrgt & "").Validation.Delete
             Me.Range("D" & RwTrgt & "").Validation.Add Type:=xlValidateList, Formula1:="=Comments!A33:A38"
            ElseIf Me.Range("C" & RwTrgt & "").Value = WsComs.Range("B32").Value Then             ' Meets Expectation
            '2d(ii) create list 3 for case Communicating Effectively and Meets Expectation
             Me.Range("D" & RwTrgt & "").Validation.Delete
             Me.Range("D" & RwTrgt & "").Validation.Add Type:=xlValidateList, Formula1:="=Comments!B33:B38"
            
            ElseIf Me.Range("C" & RwTrgt & "").Value = WsComs.Range("C32").Value Then             ' Exceeds Expectation
            '2d(iii) create list 3 for case Communicating Effectively and Exceeds Expectation
             Me.Range("D" & RwTrgt & "").Validation.Delete
             Me.Range("D" & RwTrgt & "").Validation.Add Type:=xlValidateList, Formula1:="=Comments!C33:C38"
             
            End If ' this is end of cases of  {Does Not Meet Expectation,  Meets Expectation,  Exceeds Expectation} for the case of Supporting Co-workers for Social Competencies
    '_ --------------------------------------
        End If   '  this is end of cases of  social competencies
    End If  '   This is end of checking for selected range in columns A an C ( drop down lists 1 and 2 )
End Sub