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:

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

jondu

Board Regular
Joined
Feb 6, 2019
Messages
71
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.
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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:

jondu

Board Regular
Joined
Feb 6, 2019
Messages
71
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 ?
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841

ADVERTISEMENT

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
 

jondu

Board Regular
Joined
Feb 6, 2019
Messages
71
I got an error 429"Un composant ActiveX ne peut pas créer d'objet"
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841

ADVERTISEMENT

Are you Using a "Mac" ???
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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
 

jondu

Board Regular
Joined
Feb 6, 2019
Messages
71
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 !
 

jondu

Board Regular
Joined
Feb 6, 2019
Messages
71
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,509
Messages
5,523,317
Members
409,511
Latest member
hitesh222002

This Week's Hot Topics

Top