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

**DocAElstein** · 11-08-2020, 04:59 PM

Macro for this post
https://excelfox.com/forum/showthrea...ll=1#post15085

https://imgur.com/rteyeHM https://i.imgur.com/rteyeHM.jpg http://i.imgur.com/rteyeHM.jpg

https://imgur.com/ex9FlRI https://i.imgur.com/ex9FlRI http://i.imgur.com/ex9FlRI

https://imgur.com/ZjEw5xy https://i.imgur.com/ZjEw5xy http://i.imgur.com/ZjEw5xy

Code:

Option Explicit
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


            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
        
    '_ --------------------------------------
        
        
    '2c_ --------------------------------------  Sharing Information
        
        ElseIf Me.Range("A" & RwTrgt & "").Value = WsAdv.Range("A27").Value Then             ' Sharing Information
        ' create list 4 for case Sharing Information
        
    '_ --------------------------------------
        
        
    '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
        
    '_ --------------------------------------
        
        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

Summary of coding: ( Private Sub Worksheet_Change(ByVal Target As Range) -----
https://excelfox.com/forum/showthrea...ll=1#post15087 )
Rem 1
We usually 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 from row 2 to row 8

Rem 2
In this section we go through the combinations of SOCIAL COMPETENCIES and Please Choose, and then create the appropriate drop down lists 3 and 4

‘2a) Communicating effectively
_... ‘2a(i) Does Not Meet Expectation
_... ‘2a(ii) Meets Expectation
_... ‘2a(iii) Exceeds Expectation

‘2b) Resolving Conflict
_... ‘2b(i) Does Not Meet Expectation
_... ‘2b(ii) Meets Expectation
_... ‘2b(iii) Exceeds Expectation

‘2c) Sharing Information
_... ‘2c(i) Does Not Meet Expectation
_... ‘2c(ii) Meets Expectation
_... ‘2c(iii) Exceeds Expectation

‘2d) Supporting Co-workers
_... ‘2d(i) Does Not Meet Expectation
_... ‘2d(ii) Meets Expectation
_... ‘2d(iii) Exceeds Expectation