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 me the time to discover the ComboBoxes

 

[MickG]

If you can use Comboboxes then try "Forms" Variety not "ActiveX" as they are more stable in the sheet.
Not sure they will be suitable for all your hidden rows though.
see:- Forms Combobox >Right Click (control) >Form Control > Properties (Object Positioning)

 

[jondu]

ok I give up for the dropdown list with 13 choices : makes me too much problems...
For this dropdown list : I will play with the hide/unhide row
=> when user select FR (X6), the row containing (normal) dropdown FR (row 11) is unhide (en dropdown ENG is hide)
=> when user select ENG (X6), the row containing (normal) dropdown ENG (row 12) is unhide (en dropdown FR is hide)
So the user must select his text again in X11 or X12 if he changes language. Not the best but then I can (finally) finish this work.

 

[MickG]

Have a play with these examples of Forms Comboboxes (Drop Downs) and ActiveX Combobox, Both based on data in sheet2
https://app.box.com/s/ihny8eqwrrp1r6n3ranwqygp61yb2ki0

 

[jondu]

ok looks good but my simple question is :
How do you input the data in the DROPB, DROPC and DROPD ?

Here is my steps :
- paste code VBA from your sheet into my own sheet
- create module1 and paste code Sub Dropdown1_Change
- create the sheet2 and write texts in column C to H
- create Comboboxes (in Form). For DROPA, select value FR and ENG from sheet2
- rename DROPA, DROPB, DROPC, DROPD
=> but nothing happens : the comboboxes DROPB, DROPC and DROPD are empty.

Sure it's very simple...(but I just spent 1 our to understand the steps)

 

[jondu]

and also : it it possible to customize the box not to see that we are in a combobox ? (no arrow when a choice is selected)

 

[jondu]

ok, I finished my document. I finally don't use ComboBoxes. One of my dropdown (13 choices) is not bilingual. Not ideal, but I found some arrangements.
I would like to thank you once again for your help, your availability, professionalism and clearness.
I learned a lot in the space of a few days.
All the best MickG !

 

[MickG]

You're very welcome