Dependent Drop Down Lists

**msiyab** · 11-12-2020, 10:10 AM

Originally Posted by DocAElstein

I am not sure how I can help further?

Tell me if you want me to make a start for you as I did before.

Alan

Yes please. Could you help me by starting it off for me like you had done previously. I will try the copy pasting and minor adjustments from where you leave it. Once its done on the file I have shared, i will try it on my original file.

I'd really appreciate the effort.

**DocAElstein** · 11-12-2020, 04:19 PM

Hi
I will do the easy first part now, and when I have time, possibly tomorrow , I will do the more complicated second part*

Once again we have two parts to your requirement

Part one, is fairly simple: As before, the Lists for columns A and C need to made
We just have 2x4 = 8 list ranges to make normally. As previously we can do that manually or with coding.

First two list sets ( column A and C ranges ), with coding
I have done a couple of macros for you:

Sub MakeNormalDropDowns2x4()
This macro is basically the same basic idea as the macros for Drop down Lists 1 and 2 which I have already done for you.
But now instead of creating the lists over two ranges, ( previously A2:A8 and C2:C8 in Appraisals worksheet ) , we now are doing it for the following ranges in worksheet Actual Appraisal Form
Rem 2 SOCIAL COMPETENCIES
'2a) Topic SOCIAL COMPETENCIES List 1 in column A
A26:A27
'2b) Please Choose List 2 in column C
C26:C27

Rem 3 PERSONAL COMPETENCIES
'3a) Topic PERSONAL COMPETENCIES List 1 in column A
A29:A30
'3b) Please Choose List 2 in column C
C29:C30

Rem 4 METHODOLOGICAL COMPETENCIES
'4a) Topic METHODOLOGICAL COMPETENCIES List 1 in column A
A32:A33
'4b) Please Choose List 2 in column C
C32:C33

Rem 5 LEADERSHIP COMPETENCIES
'5a) Topic LEADERSHIP COMPETENCIES List 1 in column A
A35:A36
'5b) Please Choose List 2 in column C
C35:C36

_._____________________

I have done a second alternative for you. This is just to demonstrate how we can reduce some coding sometimes using a combination of looping and the Range Offset Property
Sub MakeNormalDropDowns1x4andLoop4times()

Both those macros are here:
https://excelfox.com/forum/showthrea...ll=1#post15111

Share ‘Appraisal - Drop Down 11 11.xls’ : https://app.box.com/s/wj11tpgc9fsuoekp023cd7ndkkqyvtm1

It should be noted, that in a situation like this there is little advantages of shortening the macro. It comes down to personal choice, mostly. The performance of the two macros will be about the same.
The shorter macro looks tidier, and possibly looks a bit more clever or professional, but is likely to be less easy to modify or understand at a later date. I personally would usually use the first macro. The final file might be a bit bigger for the longer macro, but with modern computers the extra space used rarely is of great significance.

I have not checked thoroughly to see if I made any simple mistakes. I leave that to you to test thoroughly.

In my next post I will tackle the second part of your current requirement.
I will do that for you tomorrow*, or when I have more time. ….

One last thing today, – this is just out of passing interests.
This is not part of your requirement, but I did it out of my own interest a few days ago.
You might be interested to take a quick look here:
https://excelfox.com/forum/showthrea...ll=1#post15092

It is possible to make the drop down lists appear when the cell is selected
I applied that to your initial requirement, just for fun…

Alan

Share ‘Appraisal - Drop Down 11 11 First Part.xls’ : https://app.box.com/s/wj11tpgc9fsuoekp023cd7ndkkqyvtm1

**DocAElstein** · 11-13-2020, 08:16 PM

Hi
Here is the next macro for you:
Share ‘Code Appraisal - Drop Down 11 11 xls .txt’ : https://app.box.com/s/jd6mgsnd5mkwuidi2idrpf72a3d91xvq
Share ‘Appraisal - Drop Down 11 11.xls’ : https://app.box.com/s/vuggryhlalxu3qjeztkt2jby3wv9jzoj
https://pastebin.com/Avgsv1h6
https://excelfox.com/forum/showthrea...ll=1#post15118

I expect I may have made some small mistakes. I leave it to you to check thoroughly.

As you suggested, it is tedious work. But if you approach it carefully, it is not difficult.
Here some of the notes I made as I went along
https://excelfox.com/forum/showthrea...ll=1#post15114
https://excelfox.com/forum/showthrea...ll=1#post15115
https://excelfox.com/forum/showthrea...ll=1#post15116
https://excelfox.com/forum/showthrea...ll=1#post15117

Some extra suggestions
I showed you yesterday, ( Sub MakeNormalDropDowns1x4andLoop4times() ) how we can approach things slightly differently using looping and offsets.
There can be a couple of advantages to this:
_ 1. It looks a bit simpler and tidier ( it may not finally work any better )
_ 2. In some cases it might make further additions to data easier…

In order for this to work, it would be very helpful if you could have a regular structure in your data
For example, your Comment worksheet should be structured so:
Share ‘Some extra suggestions.xls’ : https://app.box.com/s/ux77egbft9kipdtiht21017y4iixp854

Similarly you should structure your Advice worksheet in a similar, regular, way.

Such regular structures would allow the easier implementation of the looping and Range.Offset coding ideas

That is just something for you to consider in the future

( When you have your final solution it would be courteous and helpful to others if you could share your solution )

Alan