Dependent Drop Down Lists

[DocAElstein]

Hello Siyab,

...... i thought dependent lists worked differently. So I thought it was better to get help from drop down 1 itself.
Drop down 3 & 4 are very tricky and is out of my league and understanding. Hopefully you can guide me through with it.

I expect your thinking is not necessarily incorrect: I would expect that there is an efficient way to look at this problem considering all 4 list together.
But I don’t know how to do it that way. I only have limited basic VBA knowledge.

So my strategy is to treat all 4 lists as independent lists created in the same way using VBA
List 1 and 2 can be either made once, or assumed to have been made.
So we will leave them made for now. Or if you start with Worksheet Appraisals having no drop down lists, then you would use this macro to make just lists 1 and 2: https://excelfox.com/forum/showthrea...ll=1#post15073


The solution I am proposing is not difficult, and is possibly not the most efficient.

Drop down List 3 and 4 will be remade / recreated by VBA coding , every time a different selection is made from drop down list 1 or 2.
When a different selection is made from list 1 or 2 , then that results in the cell to which they are associated changing its value. As far as VBA is concerned , it sees a cell change. ( VBA does not recognise you using the drop down list itself, but it does detect the cell value change).

We can use “Event coding” to detect a cell change. This can be used to trigger the re creating of the Lists 3 and 4.

We need to access the worksheet code module for worksheet Appraisals , and one of the available event codings, which is always there, but empty with no coding in it.
https://imgur.com/rteyeHM https://i.imgur.com/rteyeHM.jpg -- select the worksheet code module
https://imgur.com/ex9FlRI https://i.imgur.com/ex9FlRI.jpg -- select worksheets event codings
https://imgur.com/ZjEw5xy https://i.imgur.com/ZjEw5xy.jpg - - - we want the Worksheet_Change( ) macro


We must add coding to it, ( put coding inside it ) , for example, I have made a start for you here: https://excelfox.com/forum/showthrea...ll=1#post15087

---

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

---

So I made a start for you.
If you study the coding and get some understanding of it, then I think you can complete the macro

I will look in again in a day or two to see if you need more help



Alan