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 :

Please select a language

Dropdown list 1 (choice : 'FR' or 'ENG')

<tbody>
</tbody>

Object 1	50 Eur
Object 2	100 Eur
Dropdown list 2 (choice : 'maison' or 'avion' in FR / 'house' or 'airplane' in ENG)	150 Eur
Objet 4	200 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 ?

 

[jondu]

Oh yeah !
You are a king MickG
Unfortunately I don't have time now, but I insert the code in my own document on saturday. I keep you posted !

 

[MickG]

You're welcome

 

[jondu]

ow no.
I do the changes and then close the sheet. When I want to reopen the sheet I get this message :
"désolé...Nous avons trouvé un problème dans le contenu de "NAME", mais nous pouvons essayer de récupérer le maximum du contenu. Si la source de ce classeur est fiable, cliquez sur Oui."
Then I click YES the sheet open and is empty : so all my changes are lost ! (so much time...)
Any idea ?

 

[jondu]

ok no sorry (I did not see well) : it's not that the sheet is empty, it's just that I have to reinitialise manually the 4 dropdown lists. Still annoying...

 

[jondu]

So precisions, after re-open and clicked YES at the error message, I have to (every time) :
- reset manually the dropdown lists
- copy the VBA code wich is in another new sheet in Microsoft Visual Basic and paste it in my active sheet
- rename manually the cells (Data1, Data2 etc.) in my sheet (names have disappeared)
So there is something wrong...

 

[jondu]

Ok after many tests, I understand that the problem is related to the length of the options in columns A, B, C of Sheet2 (instead of 'fr1' and 'eng1' for example, I have small sentences)

 

[jondu]

and it's not related to accents or spaces, just the length (not the length of a single option, but the accumulation of several long options)

 

[MickG]

Just reading your latest !!

 

[jondu]

Yes, and I tried with your own file : does the same error (due to length of option)

 

[MickG]

There's plenty of information on limitations of validation lists on line:_
https://answers.microsoft.com/en-us...in-excel/9ce4a909-8b03-428f-94a4-1b13433aa399

You could possibly do this with ComboBoxes ???