Automatically update of secondary cell entry in cascading dropdown menu

jondu

Board Regular
Joined
Feb 6, 2019
Messages
71
Hello,
I have a list of price in 2 languages : French (FR) en English (ENG).
Here is a model :


Please select a language
Dropdown list 1 (choice : 'FR' or 'ENG')

<tbody>
</tbody>


Object 150 Eur
Object 2100 Eur
Dropdown list 2 (choice : 'maison' or 'avion' in FR / 'house' or 'airplane' in ENG)150 Eur
Objet 4200 Eur

<tbody>
</tbody>

This price-list contains also choices, with dropdown menu list. Example here : the dropdown list 2.
If the selected language is FR (dropdown 1) => the dropdown 2 must show the choice 'maison' or 'avion' in FR
If the selected language is ENG (dropdown 1) => the dropdown 2 must show the choice 'house' or 'airplane' in FR
So i speak about cascading dynamic dropdown. Not so difficult.

BUT the thing is that I want that the previously selected choice in dropdown 2 change automatically language when value dropdown 1 is changed.
Example :
Dropdown 1 value = 'ENG'
Dropdown 2 value = 'airplane'
If dropdown 1 value is changed in 'FR', the cell of dropdown 2 should display automatically 'avion'.
The user must NOT reset manually his choice in dropdown 2.

Any ideas ?
 
Last edited:

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
ok let's be more simple.

DROPDOWN LIST 1 : Choice 'FR' or 'ENG'
DROPDOWN LIST 2 : Choice 'House' or 'Avion (FR) OR 'Maison' or 'Airplane' (ENG)
= Dependent drodpowns.

=> If 'House' chosen in dropdown list 2 (when dropdown list 1 = ENG), it must turn automatically 'Maison' when the user select 'FR' in dropdown list 1 (switching language).

Any help very appreciated.
 
Upvote 0
Place validation list in "A1" with "FR & ENG" in.
To load code:-

Right click Sheet Tab > From Menu, select "View Code" >, VB window appears> Paste code into vbWindow > Close Vbwindow.
Cell "B1" will now alter dependent on cell "A1".

Code:
Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] nStr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]If[/COLOR] Target.Address(0, 0) = "A1" [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] Target
        [COLOR="Navy"]Case[/COLOR] "FR": nStr = "Avion,Maison"
        [COLOR="Navy"]Case[/COLOR] "ENG": nStr = "House,Airoplane"
    [COLOR="Navy"]End[/COLOR] Select
[COLOR="Navy"]With[/COLOR] Range("B1").Validation
    .Delete
    .Add Type:=xlValidateList, Formula1:=nStr 
[COLOR="Navy"]End[/COLOR] With

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Last edited:
Upvote 0
Thank you MickG,
I find it always surprising that people help strangers/unknown people, very nice.
Your solution works well, but it's only classic dependant dropdown list.
The objectif is :
when A1 value = FR and B1 value = avion
then B1 value must turn automatically 'airplane' if A1 value in ENG

Could you help me more with this ?
 
Upvote 0
Try this:-

Code:
Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] ray [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] nStr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
ray = [{"Avion","AiroPlane";"AiroPlane","Avion";"Maison","House";"House","Maison"}]
Txt = [b1]

[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
        [COLOR="Navy"]For[/COLOR] n = 1 To UBound(ray): .Item(ray(n, 1)) = ray(n, 2): [COLOR="Navy"]Next[/COLOR] n

    [COLOR="Navy"]If[/COLOR] Target.Address(0, 0) = "A1" [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] Target
            [COLOR="Navy"]Case[/COLOR] "FR": nStr = "Avion,Maison"
            [COLOR="Navy"]Case[/COLOR] "ENG": nStr = "House,Airoplane"
        [COLOR="Navy"]End[/COLOR] Select

[COLOR="Navy"]With[/COLOR] Range("B1").Validation
    .Delete
    .Add Type:=xlValidateList, Formula1:=nStr
[COLOR="Navy"]End[/COLOR] With
 
Range("B1").Value = .Item(Txt)
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Try this:-
Code:
Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] ray [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] nStr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
Application.EnableEvents = False
[COLOR="Navy"]If[/COLOR] Target.Address(0, 0) = "A1" [COLOR="Navy"]Then[/COLOR]
        ray = [{"Avion","Airoplane";"Airoplane","Avion";"Maison","House";"House","Maison"}]
        Txt = [b1]
        [COLOR="Navy"]For[/COLOR] n = 1 To UBound(ray, 1)
            [COLOR="Navy"]If[/COLOR] ray(n, 1) = Txt [COLOR="Navy"]Then[/COLOR]
                Txt = ray(n, 2)
                [COLOR="Navy"]Exit[/COLOR] For
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] n
        
        [COLOR="Navy"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] Target
            [COLOR="Navy"]Case[/COLOR] "FR": nStr = "Avion,Maison"
            [COLOR="Navy"]Case[/COLOR] "ENG": nStr = "House,Airoplane"
        [COLOR="Navy"]End[/COLOR] Select

[COLOR="Navy"]With[/COLOR] Range("B1").Validation
    .Delete
    .Add Type:=xlValidateList, Formula1:=nStr
[COLOR="Navy"]End[/COLOR] With
 
Range("B1").Value = Txt
[COLOR="Navy"]End[/COLOR] If
Application.EnableEvents = True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Wouah, unbelievable. It's works. I thought it was impossible. Thank you so much.

I'm using a mac indeed (at home) but I need this code for a Windows (at work) (I thought it was the same codes). I'll try the code for Windows tomorrow then.
What if I have more dropdown, and some with more choices ? (for Windows)
Ex :
DROPDOWN 1 (in X7) : FR or ENG
DROPDOWN 2 (in X26) : avion/maison (fr) OR airplane/house (eng)
DROPDOWN 3 (in X79) : musique/université/rectangulaire/fourniture (fr) OR music/university/rectangular/furniture (eng)
I would then understand the logic I think.

Thank you again !
 
Upvote 0
And also : is it possible to be more flexible in the cell number ? Ex : if I insert a row before row 1, the cells A1 and B1 become the new A2 and B2 and the code is not actif anymore. The user must be able to insert or delete row as much as he wants.
If this request is not possible it is optional (but anyway desired ). Thank you.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top