Automatically update of secondary cell entry in cascading dropdown menu

[jondu]

Hello,
I have a list of price in 2 languages : French (FR) en English (ENG).
Here is a model :


[TABLE="width: 500"]
<tbody>[TR]
[TD]Please select a language[/TD]
[/TR]
[TR]
[TD]Dropdown list 1 (choice : 'FR' or 'ENG')[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 500"]
<tbody>[TR]
[TD]Object 1[/TD]
[TD]50 Eur[/TD]
[/TR]
[TR]
[TD]Object 2[/TD]
[TD]100 Eur[/TD]
[/TR]
[TR]
[TD]Dropdown list 2 (choice : 'maison' or 'avion' in FR / 'house' or 'airplane' in ENG)[/TD]
[TD]150 Eur[/TD]
[/TR]
[TR]
[TD]Objet 4[/TD]
[TD]200 Eur[/TD]
[/TR]
</tbody>[/TABLE]

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 ?

 

[jondu]

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]

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".

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

 

[jondu]

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]

Try this:-

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]

I got an error 429"Un composant ActiveX ne peut pas créer d'objet"

 

[MickG]

Are you Using a "Mac" ???

 

[MickG]

Try this:-

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]

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]

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.