continued from last post - Some extra notes in development of answer for this post
https://excelfox.com/forum/showthrea...ll=1#post15119
( second complicated part: lists 3 and 4 based on choice from Lists 1 and 2 )
Code:Private Sub Worksheet_Change(ByVal Target As Range) Rem 1 worksheets info Dim WsApp As Worksheet, WsComs As Worksheet, WsActual As Worksheet, WsAdv As Worksheet Set WsAdv = ThisWorkbook.Worksheets("Advise"): Set WsApp = ThisWorkbook.Worksheets("Appraisal"): Set WsComs = ThisWorkbook.Worksheets("Comments"): Set WsActual = ThisWorkbook.Worksheets("Actual Appraisal Form") Dim RwTrgt As Long: Let RwTrgt = Target.Row ' Rem 2 Rem 3 Rem 4 Rem5 Topics, determined by row selection in columns A and C ------------------------------------------- If Not Application.Intersect(Target, Me.Range("A26:A27,C26:C27")) Is Nothing Then ' Not nothing means we changed something in A26:A27 or C26:C27 Rem 2 Topic: SOCIAL COMPETENCIES '2a_ -------------------------------------- Communicating effectively ' create list 4 Advice ' Now go through the 3 Choose Options '2a(i) create list 3 Does Not Meet Expectation '2a(ii) create list 3 Meets Expectation '2a(iii) create list 3 Exceeds Expectation '2b_ -------------------------------------- Resolving Conflict ' create list 4 Advice ' Now go through the 3 Choose Options '2b(i) create list 3 Does Not Meet Expectation '2b(ii) create list 3 Meets Expectation '2b(iii) create list 3 Exceeds Expectation '2c_ -------------------------------------- Sharing Information ' create list 4 Advice ' Now go through the 3 Choose Options '2c(i) create list 3 for case Does Not Meet Expectation '2c(ii) create list 3 for case Meets Expectation '2c(iii) create list 3 for case Exceeds Expectation '2d_ -------------------------------------- Supporting Co-workers ' create list 4 Advice ' Now go through the 3 Choose Options '2d(i) create list 3 for case Does Not Meet Expectation '2d(ii) create list 3 for case Meets Expectation '2d(iii) create list 3 for case Exceeds Expectation ' this is end of Topic social competencies ElseIf Not Application.Intersect(Target, Me.Range("A29:A30,C29:C30")) Is Nothing Then Rem 3 Topic: PERSONAL COMPETENCIES '3a_ -------------------------------------- Adapting to Change ' create list 4 Advice ' Now go through the 3 Choose Options '3a(i) create list 3 Does Not Meet Expectation '3a(ii) create list 3 Meets Expectation '3a(iii) create list 3 Exceeds Expectation '3b_ -------------------------------------- Demonstrating Tenacity and Perseverance ' create list 4 Advice ' Now go through the 3 Choose Options '3b(i) create list 3 Does Not Meet Expectation '3b(ii) create list 3 Meets Expectation '3b(iii) create list 3 Exceeds Expectation '3c_ -------------------------------------- Following Policies and Procedures ' create list 4 Advice ' Now go through the 3 Choose Options '3c(i) create list 3 for case Does Not Meet Expectation '3c(ii) create list 3 for case Meets Expectation '3c(iii) create list 3 for case Exceeds Expectation '3d_ -------------------------------------- Learning Quickly ' create list 4 Advice ' Now go through the 3 Choose Options '3d(i) create list 3 for case Does Not Meet Expectation '3d(ii) create list 3 for case Meets Expectation '3d(iii) create list 3 for case Exceeds Expectation '3e_ -------------------------------------- Pursuing Self-Development ' create list 4 Advice ' Now go through the 3 Choose Options '3e(i) create list 3 for case Does Not Meet Expectation '3e(ii) create list 3 for case Meets Expectation '3e(iii) create list 3 for case Exceeds Expectation '3f_ -------------------------------------- Supporting Organizational Goals ' create list 4 Advice ' Now go through the 3 Choose Options '3f(i) create list 3 for case Does Not Meet Expectation '3f(ii) create list 3 for case Meets Expectation '3f(iii) create list 3 for case Exceeds Expectation ' this is end of Topic PERSONAL COMPETENCIES ElseIf Not Application.Intersect(Target, Me.Range("A32:A33,C32:C33")) Is Nothing Then Rem 4 Topic: METHODOLOGICAL COMPETENCIES '4a_ -------------------------------------- Evaluating and Implementing Ideas ' create list 4 Advice ' Now go through the 3 Choose Options '4a(i) create list 3 Does Not Meet Expectation '4a(ii) create list 3 Meets Expectation '4a(iii) create list 3 Exceeds Expectation '4b_ -------------------------------------- Managing Time ' create list 4 Advice ' Now go through the 3 Choose Options '4b(i) create list 3 Does Not Meet Expectation '4b(ii) create list 3 Meets Expectation '4b(iii) create list 3 Exceeds Expectation '4c_ -------------------------------------- Prioritizing and Organizing Work ' create list 4 Advice ' Now go through the 3 Choose Options '4c(i) create list 3 for case Does Not Meet Expectation '4c(ii) create list 3 for case Meets Expectation '4c(iii) create list 3 for case Exceeds Expectation '4d_ -------------------------------------- Solving Complex Problems ' create list 4 Advice ' Now go through the 3 Choose Options '4d(i) create list 3 for case Does Not Meet Expectation '4d(ii) create list 3 for case Meets Expectation '4d(iii) create list 3 for case Exceeds Expectation ' this is end of Topic METHODOLOGICAL COMPETENCIES ElseIf Not Application.Intersect(Target, Me.Range("A35:A36,C35:C36")) Is Nothing Then Rem 5 Topic: LEADERSHIP COMPETENCIES '5a_ -------------------------------------- Accepting Responsibility Acting Strategically ??????? ' create list 4 Advice ' Now go through the 3 Choose Options '5a(i) create list 3 Does Not Meet Expectation '5a(ii) create list 3 Meets Expectation '5a(iii) create list 3 Exceeds Expectation '5b_ -------------------------------------- Delegating Responsibility ' create list 4 Advice ' Now go through the 3 Choose Options '5b(i) create list 3 Does Not Meet Expectation '5b(ii) create list 3 Meets Expectation '5b(iii) create list 3 Exceeds Expectation '5c_ -------------------------------------- Developing Talent ' create list 4 Advice ' Now go through the 3 Choose Options '5c(i) create list 3 for case Does Not Meet Expectation '5c(ii) create list 3 for case Meets Expectation '5c(iii) create list 3 for case Exceeds Expectation '5d_ -------------------------------------- Driving for Results ' create list 4 Advice ' Now go through the 3 Choose Options '5d(i) create list 3 for case Does Not Meet Expectation '5d(ii) create list 3 for case Meets Expectation '5d(iii) create list 3 for case Exceeds Expectation '5e_ -------------------------------------- Inspiring and Motivating Others ' create list 4 Advice ' Now go through the 3 Choose Options '5e(i) create list 3 for case Does Not Meet Expectation '5e(ii) create list 3 for case Meets Expectation '5e(iii) create list 3 for case Exceeds Expectation '5f_ -------------------------------------- Managing Performance ' create list 4 Advice ' Now go through the 3 Choose Options '5f(i) create list 3 for case Does Not Meet Expectation '5f(ii) create list 3 for case Meets Expectation '5f(iii) create list 3 for case Exceeds Expectation ' this is end of Topic LEADERSHIP COMPETENCIES Else ' ' we come here if had changed something anywhere else other than ranges A26:A27,C26:C27, A29:A30,C29:C30, A32:A33,C32:C33, A35:A36,C35:C36 End If ' This is the end of all Topics ' Énd of all Topics ------------------------------------------------------------------------------------------------------ End Sub
At lot of the tedious typing can be simplifies by copy ( Ctrl+c ) and pasting ( Ctrl+v ) and sometimes further the modification can be done easier using tools such as Search and Replace on highlighted text
SearchReplaceInVBEditor.JPG
https://imgur.com/4Ou7Q6q https://i.imgur.com/4Ou7Q6q.jpg
Note: Make sure you select Highlighted text
SearchReplaceInVBEditorOnHighlightedText.JPG
https://imgur.com/Wvw7Ol1.jpg https://i.imgur.com/Wvw7Ol1.jpg
Reply With Quote
Bookmarks