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

**DocAElstein** · 11-06-2020, 11:05 AM

Code:

Sub test()
    SendKeys "%s{Enter}"
    MsgBox ("please just click OK")
End Sub

' https://excelfox.com/forum/showthrea...ll=1#post14885
' https://excelfox.com/forum/showthrea...ll=1#post14877
' https://excelfox.com/forum/showthrea...cting-the-Cell
' http://www.eileenslounge.com/viewtop...277023#p277023

**DocAElstein** · 11-06-2020, 11:06 AM

Insupport of this Thread post:
https://excelfox.com/forum/showthrea...5085#post15085

Code:

Sub MakeDropDownList1and2()  '   https://excelfox.com/forum/showthread.php/2676-Dependent-Drop-Down-Lists?p=15085&viewfull=1#post15085
Rem 1 worksheets info
Dim WsApp As Worksheet, WsComs As Worksheet
 Set WsApp = ThisWorkbook.Worksheets("Appraisal"): Set WsComs = ThisWorkbook.Worksheets("Comments")
Rem 2 FirstTwoDropDowns2
' 2a) List 1 in column A
 WsApp.Range("A2:A8").Validation.Delete
 WsApp.Range("A2:A8").Validation.Add Type:=xlValidateList, Formula1:="" & WsComs.Range("A1").Value & "," & WsComs.Range("A11").Value & "," & WsComs.Range("A21").Value & "," & WsComs.Range("A31").Value & ""

'2b) list 2 in column C
 WsComs.Range("C2:C8").Validation.Delete
 WsComs.Range("C2:C8").Validation.Add Type:=xlValidateList, Formula1:="" & WsComs.Range("A2").Value & "," & WsComs.Range("B2").Value & "," & WsComs.Range("C2").Value & ""
End Sub

**DocAElstein** · 11-06-2020, 12:01 PM

Notes in support of this thread:
https://excelfox.com/forum/showthrea...rop-Down-Lists
Following approximately these steps:
https://support.microsoft.com/en-us/...9-c5402dce407b
Worksheets info from OP:
Requirements:

1. Under "Social Competencies", the drop down list should contain all the headers from the sheet "Comments" (Color Code - Peach).

2. Under "Please Choose", the drop down list should contain "Does Not Meet Expectation", "Meets Expectation", "Exceeds Expectation" (Color Code - Grey).

3. So, If a person selects "Communicating Effectively" under Social Competencies column, and then selects "Meets Expectation" in the next column, then the drop down list on Column D "Please Choose" should display the list from B3:B8 from the sheet named "Comments".

Another example would be if a person selects "Resolving Conflict" under Social Competencies column, and then selects "Does Not Meet Expectation" in the next column, then the drop down list on Column D "Please Choose" should display the list from A13:A18 from the sheet named "Comments".

4. So, If a person selects "Sharing Information" under Social Competencies column, then the drop down list on Column E "Give Advise" should display the list from A28:A32 from the sheet named "Give Advise"
_____ Workbook: Appraisal - Drop Down.xls ( Using Excel 2007 32 bit )

_Row\^Col	A	C	D	E
1	SOCIAL COMPETENCIES	Please Choose	Comments	Give Advise
2
3
4
5
6
7
8

Worksheet: Appraisal

Continued in next post…._

**DocAElstein** · 11-06-2020, 12:02 PM

Continued in next post…._

_..... continued from last post
Worksheets info from OP:
Requirements:

1. Under "Social Competencies", the drop down list should contain all the headers from the sheet "Comments" (Color Code - Peach).

2. Under "Please Choose", the drop down list should contain "Does Not Meet Expectation", "Meets Expectation", "Exceeds Expectation" (Color Code - Grey).

3. So, If a person selects "Communicating Effectively" under Social Competencies column, and then selects "Meets Expectation" in the next column, then the drop down list on Column D "Please Choose" should display the list from B3:B8 from the sheet named "Comments".

Another example would be if a person selects "Resolving Conflict" under Social Competencies column, and then selects "Does Not Meet Expectation" in the next column, then the drop down list on Column D "Please Choose" should display the list from A13:A18 from the sheet named "Comments".

4. So, If a person selects "Sharing Information" under Social Competencies column, then the drop down list on Column E "Give Advise" should display the list from A28:A32 from the sheet named "Give Advise"

_____ Workbook: Appraisal - Drop Down.xls ( Using Excel 2007 32 bit )

_Row\^Col	A	B	C
1	Communicating Effectively
2	Does Not Meet Expectation	Meets Expectation	Exceeds Expectation
3	Assumes understanding.	Actively listens.	Encourages others to share information.
4	Difficult to contact.	Communicates regularly with team members.	Excellent listener.
5	Does not adapt language to listener.	Communicates with team members.	Gives people full attention.
6	Does not give others full attention.	Conveys enthusiasm.	Promotes candid and open atmosphere.
7	Ideas and comments are hard to follow.	Gives people full attention.	Shares information with team.
8	Uses overly complex language.	Shares information with team.	Shares useful information.
9
10
11	Resolving Conflict
12	Does Not Meet Expectation	Meets Expectation	Exceeds Expectation
13	Becomes emotional dealing with conflict.	Avoids emotional involvement.	Diffuses conflicts before they start.
14	Gives up easily, misses opportunities.	Avoids unnecessary conflict.	Finds permanent fixes to tough conflicts.
15	Is caught off guard by conflicts.	Handles difficult people.	Handles difficult people easily.
16	Needs to focus on problems, not people.	Helps others end disputes.	Listens to all, hears all sides.
17	Shies away from conflict, lets it escalate.	Tries to find roots of conflict.	Top peacekeeper, solution-finder.
18	Too confrontational, focused on win-lose.	Uses good listening, communication skills.
19
20
21	Sharing Information
22	Does Not Meet Expectation	Meets Expectation	Exceeds Expectation
23	Does not communicate experience or knowledge to others.	Effectively communicates information and keeps teammates on the same page.	Carefully listens to what others have to say and adds insightful comments; asks important questions.
24	Does not listen to others.	Keep key players informed.	Conveys extremely complex information in a simple fashion.
25	Does not share relevant information to the team.	Knows the appropriate amount of knowledge to share with others.	Does not act aloof or arrogant, is humble and happy to help.
26	Does not speak up.	Prior to presenting and idea or sharing information, run it by someone first.	Helps foster and cultivate an environment that is open to exchange of information.
27	Shares too little information.	Shares useful information with teammates.	Informs the team with specific information to achieve and surpass goals.
28	Shares useless information.	Willingly shares leadership skills and knowledge with others.	Provides expert information and knowledge with teammates.
29
30
31	Supporting Co-workers
32	Does Not Meet Expectation	Meets Expectation	Exceeds Expectation
33	Could be more collaborative.	Appreciates others.	Able to resolve conflict.
34	Could be more open-minded.	Collaborates with others.	Excellent listening skills.
35	Does not respond to others' needs.	Helps resolve team conflicts.	Flexible and open-minded.
36	does not support new members	Proactively share information and suggestions to help new team members get up to speed as fast as possible.	Includes others.
37	Excessively critical of others.	Welcomes new team members.	Promotes team spirit.
38	Rarely accommodates others.	Willing to help.	Provides support.

Worksheet: Comments
Continued in next post…._

**DocAElstein** · 11-06-2020, 12:05 PM

_..........Continued from last post

_____ Workbook: Appraisal - Drop Down.xls ( Using Excel 2007 32 bit )

_Row\^Col	A
1	Communicating Effectively
2	Effective communication can be challenging. Often, ineffective communication results from misunderstanding. Try not to get discouraged if at first others do not understand what you are saying. Make your best efforts to restate and clarify.
3	Remember that effective communication requires listening skills. Think about how you would like others to listen when you are trying to explain something. Be that listener for others.
4	When communicating with others, try to gauge your audience effectively. Gather adequate information in order to communicate as effectively as possible.
5	Spend time organizing your thoughts before you write a document or give a presentation. Draw pictures or a timeline if that helps. Determine where you want your audience to be at the conclusion. Try to identify the optimal steps to get your audience where you want them to be.
6	Rehearse and practice before giving presentations. Individuals who practice prior to presenting appear to be more effective communicators than those individuals who do not.
7	Be sure to give your full attention to others. If you schedule a time to meet with someone be sure to actively be present.
8	Take on projects where you can be the lead. This will allow you to help you hone your communication skills.
9	Take on projects where a final document or presentation will be given to your teammates or superiors.
10	Books you may find useful: Ekman, P. (2007). Emotions Revealed, Second Edition: Recognizing Faces and Feelings to Improve Communication and Emotional Life.; Alder, R. B., Elmhorst, J. M. (2008). Communicating at Work: Principles and Practices for Business and the Professions.; Alessandra, T. (1993). Communicating at Work.
11	Books you may find useful: Lewis, G. (2000). The Mentoring Manager: Strategies for Fostering Talent and Spreading Knowledge.; Ingwersen, P., Jarvelin, K. (2005). The Turn: Integration of Information Seeking and Retrieval in Context.
12
13
14	Resolving Conflict
15	One difficulty in dealing with conflict is doing so without becoming emotionally invested. Highly emotional people may struggle with seeing the situation objectively. When people feel personally invested, it will become difficult to reach a resolution.
16	It is common for people who struggle with negotiation to also struggle with conflict resolution. This is not surprising because both involve working with others to reach a common ground. Negotiation is often a critical skill in resolving conflict. It may be beneficial to try improving your negotiation skills in order to more effectively manage conflict.
17	Wait a few seconds (or days) before answering. When a person says something to you that gets under your skin, give yourself the time you need to respond objectively and not react impulsively. That may take a couple seconds or it may require that you table the issue until you've had time or are in a better frame of mind to deal with it constructively.
18	Put yourself in another person's position. Why is this person acting confrontational? What do they really want? What pressure is on them? Try to look a level deeper to find a point from which you can start a more productive conversation.
19	Keep communication open. Get in the habit of asking all participants, especially the least vocal attendees, for their input. A simple """"""""What's your take on this, Bill?"""""""" or """"""""What do you think about that idea, Judy?"""""""" can often bring out valuable opinions or underlying issues. In order to do this, you'll also have to limit the time each person has to speak.
20	Give your input objectively. Whether or not you feel intensely involved in the conflict, state your opinions on the issues objectively and independently. Avoid the appearance of taking sides. If your opinion is clearly on one side or the other, try to state it in terms different than those used by the disagreeing parties.
21	Fix the process. Many conflicts and aggravation can be caused by systems and processes, or lack thereof. Work with the people involved to locate the sticking points and help them figure out how to change the process if it is causing problems.
22	Try taking on a position as a conflict mediator. This can help you to see how conflicts tend to escalate, and how they tend to be resolved in the end. The more you observe conflicts and help others to resolve them, the easier it will become to use the same techniques in your own interactions.
23	Work with people. Conflict resolution is an interpersonal skill that must be developed by interacting with people. Experience with people will help improve your understanding of interpersonal relationships. This will help you to better resolve conflicts as well as to avoid them in the first place.
24	Books you may find useful: Isenhart, Myra and Spangle, Michael. Collaborative Approaches to Resolving Conflict. Sage Publications, 2000; Yankelovich, Daniel. The Magic of Dialogue: Transforming Conflict into Cooperation. Simon & Schuster, Inc., 1999; Mayer, Bernard. Beyond Neutrality: Confronting the Crisis to Conflict Resolution. John Wiley & Sons, Inc., 2004.
25
26
27	Sharing Information
28	Examine your experiences and determine if there is pertinent information you can share with the group. If there is, find an appropriate time to speak up. On every team you are a part of you provide not only a unique set of skills, but also unique experiences that may hold to the answer to a problem.
29	Figure out the best way for you to actively engage in discussion. Help create an environment that is best for you and others to share their ideas. Identify teams and people with whom you are most comfortable to work. Learn to work with those individuals you are not as comfortable working with.
30	Speak your opinion without stepping on other people's toes. Try to be constructively critical of ideas and build off of them.
31	Share enough information to accomplish the goal, but don't overload people. If you can tell people are not retaining information then be more specific and less detailed.
32	Resist the temptation to sit back. Force yourself to speak up at each meeting. Keep your focus on the main goal.
33
34
35	Supporting Co-workers
36	Inspiring and motivating others effectively requires that you be motivated as well. People will not respond well to being told to do something their boss is not willing to do. If you are not enthusiastic and don't challenge yourself, people will recognize this and their motivation will suffer.
37	Focusing too heavily on your own tasks without considering others can cause you to seem uncommitted to the members of your team. It is important that your team feel valued or you will struggle with motivating them to reach their goals. This requires taking a regular and active interest in the work they are doing, including helping them address and overcome challenges and obstacles.
38	Appeal to your audience. Think about what is important to the people you'll be talking to about the organization's vision. Are they people who will want to get down to the details? Or do they only have a minute to understand what you're talking about? Think about it and modify your pitch to what will be most effective.
39	Make a conscious effort to check your plans against organizational goals and the overall vision. Keep checking the plans as they evolve to make sure they continue to support long-term organizational interests.
40	Expand organizational contacts. Use informal contacts in other groups and departments to broaden your knowledge of organizational operations. Develop new contacts who can give you new insight into the company's goals and help you better understand how your work fits into the broader organizational strategy.
41	Be positive, enthusiastic. Remember that your attitude and mood can affect others no matter what you are saying. Smile, be energetic when you speak, look people in the eye. The more enthusiastic you are about your ideas, the more committed others are likely to be in supporting you.
42	Look at needs and wants. The key to managing and improving performance lies in understanding each employees' needs and wants and showing how their tasks and assignments relate to their personal career goals and desires. Simply taking time to sincerely talk with people about their job satisfaction, engagement, and career ambitions can help increase their sense of motivation.
43	Share responsibility for all outcomes. If the outcome is good, share praise and rewards. If the outcome is bad, let individuals know what the consequences are and how they could improve their part in the future.
44	Not everyone values the same rewards. Learn what motivates your people. Use a variety of methods to reward people including public recognition, financial incentives, work assignments that match their goals, or other ways to provide them with things they value. Don't promise more than you can deliver, but be creative in what you promise.
45	Recognize achievements immediately. Don't delay rewarding hard work. Make sure that your appreciation is given while accomplishments are still fresh in someone's mind.
46	Make your criticism constructive and actionable. When someone has made a mistake, don't talk to them before you've come up with at least one specific thing they can do to improve. Get in the habit of giving all employees specific incident-oriented criticism. Show the employee the steps to take to be successful, and show your interest in their success.
47	Encourage staff to step up and set high standards for themselves. Make it clear that setting ambitious goals and reaching them is something that will be appreciated and rewarded in your organization.
48	Books you may find useful: Staley, M.F. (1998). Igniting the Leader Within: Inspiring, Motivating, & Influencing Others. Saddle Brook: Fire Engineering Books & Videos; Nair, K. (1993). Leadership as Service. Audiobook: Stanford Alumni Association; Bruce, A., & Pepitone, J.S. (1999). Motivating Employees. New York: McGraw-Hill Companies, Inc.

Worksheet: Advise

**DocAElstein** · 11-06-2020, 12:07 PM

Notes in support of this thread:
https://excelfox.com/forum/showthrea...rop-Down-Lists
Following approximately these steps:
https://support.microsoft.com/en-us/...9-c5402dce407b
Worksheets info from OP:
Requirements:

1. Under "Social Competencies", the drop down list should contain all the headers from the sheet "Comments" (Color Code - Peach).

2. Under "Please Choose", the drop down list should contain "Does Not Meet Expectation", "Meets Expectation", "Exceeds Expectation" (Color Code - Grey).

3. So, If a person selects "Communicating Effectively" under Social Competencies column, and then selects "Meets Expectation" in the next column, then the drop down list on Column D?? "Please Choose" should display the list from B3:B8 from the sheet named "Comments".

Another example would be if a person selects "Resolving Conflict" under Social Competencies column, and then selects "Does Not Meet Expectation" in the next column, then the drop down list on Column D "Please Choose" should display the list from A13:A18 from the sheet named "Comments".

4. So, If a person selects "Sharing Information" under Social Competencies column, then the drop down list on Column E "Give Advise" should display the list from A28:A32 from the sheet named "Give Advise"

( ?? typo – probably should be C)

See screenshots in last three posts for worksheets info
https://excelfox.com/forum/showthrea...ll=1#post15074
https://excelfox.com/forum/showthrea...ll=1#post15075
https://excelfox.com/forum/showthrea...ll=1#post15076

I initially made some temporary ranges, -

_____ Workbook: Appraisal - Drop Down.xls ( Using Excel 2007 32 bit )

_Row\^Col	E	F	G
1
2	Communicating Effectively
3	Resolving Conflict
4	Sharing Information
5	Supporting Co-workers
6
7	Does Not Meet Expectation	Meets Expectation	Exceeds Expectation
8

Worksheet: Comments

I then ran a macro recording whilst following approximately the instructions here: https://support.microsoft.com/en-us/...9-c5402dce407b

This is approximately what the macro recorder produced. ( I added Later some 'note comments )

Code:

Sub Macro2()   '    https://excelfox.com/forum/showthread.php/2561-Appendix-Thread-(-Codes-for-other-Threads-etc-)-Event-Coding-Drpdown-Data-validation?p=15077&viewfull=1#post15077
'In a new worksheet, type the entries you want to appear in your drop-down list. - I made some temporary ranges in second worksheet
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "Communicating Effectively"
    Range("E4").Select
    ActiveCell.FormulaR1C1 = "Sharing Information"
    Range("E5").Select
    ActiveCell.FormulaR1C1 = "Supporting Co-workers"
    Range("E6").Select
    Range("A12:C12").Select
    Selection.Copy
    Range("E7").Select
    ActiveSheet.Paste

    Application.CutCopyMode = False
    
'Select the range in the worksheet where you want the drop down list
    Range("A2:A8").Select
'Go to the Data tab on the Ribbon, then Data Validation. 'On the Settings tab, in the Allow box, click List. 'Click in the Source box, then select your list range. Click  OK
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Comments!E2:E5"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    
    Range("C2:C8").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Comments!E7:G7"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""

End Sub

**sandy666** · 11-06-2020, 12:14 PM

I see you're a famous writer

**DocAElstein** · 11-06-2020, 12:55 PM

All my codings and postings are beautiful
I am a writer, and Artist also.

**DocAElstein** · 11-06-2020, 02:13 PM

Notes in support of this thread:
https://excelfox.com/forum/showthrea...rop-Down-Lists

Here are a few attempts at a macro , based on the macro produced from the macro recorder in the last post.

Code:

Sub MakeFirstTwoDropDowns1()   '
    With ThisWorkbook.Worksheets("Appraisal").Range("A2:A8").Validation
     .Delete
     '.Add Type:=xlValidateList, Formula1:="=Comments!E2:E5"
     .Add Type:=xlValidateList, Formula1:="Communicating Effectively,Resolving Conflict,Sharing Information,Supporting Co - workers"
    End With
    With ThisWorkbook.Worksheets("Appraisal").Range("C2:C8").Validation
     .Delete
     '.Add Type:=xlValidateList, Formula1:="=Comments!E2:E5"
     .Add Type:=xlValidateList, Formula1:="Does Not Meet Expectation,Meets Expectation,Exceeds Expectation"
    End With
End Sub
Sub MakeFirstTwoDropDowns2()
Rem 1 worksheets info
Dim WsApp As Worksheet, WsComs As Worksheet
 Set WsApp = ThisWorkbook.Worksheets("Appraisal"): Set WsComs = ThisWorkbook.Worksheets("Comments")
Rem 2 FirstTwoDropDowns2
 WsApp.Range("A2:A8").Validation.Delete
 'WsApp.Range("A2:A8").Validation.Add Type:=xlValidateList, Formula1:="Communicating Effectively,Resolving Conflict,Sharing Information,Supporting Co - workers"
 WsApp.Range("A2:A8").Validation.Add Type:=xlValidateList, Formula1:="" & WsComs.Range("A1").Value & "," & WsComs.Range("A11").Value & "," & WsComs.Range("A21").Value & "," & WsComs.Range("A31").Value & ""

 WsComs.Range("C2:C8").Validation.Delete
 'WsComs.Range("C2:C8").Validation.Add Type:=xlValidateList, Formula1:="Does Not Meet Expectation,Meets Expectation,Exceeds Expectation"
 WsComs.Range("C2:C8").Validation.Add Type:=xlValidateList, Formula1:="" & WsComs.Range("A2").Value & "," & WsComs.Range("B2").Value & "," & WsComs.Range("C2").Value & ""
End Sub