Automatically update of secondary cell entry in cascading dropdown menu

J

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 :


[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 ?
 
Last edited:
Sort by date Sort by votes
oups, ok nice.
I would like now to add another dropdown list (Data4, in X11) with 13 choices (in 2 languages).
I get the logic for Range and nray etc. but I think I still miss something...
I'm really done after that.
 
Last edited:
Upvote 0

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Here is my attempt (with does not work)
Here old Data2 has become Data3 and old Data3 has become Data4. There is a new Data2 for the new dropdown list (13 choices)

Code: 
Private Sub Worksheet_Change (ByVal Target As Range)
Dim ray As Variant, n As Long, ac As Long, Txt As String, nStr As String
Application.EnableEvents = False
ReDim ray(1 To 4, 1 To 2)
ReDim nray(1 To 8, 1 To 2)
If Target.Address = Range("Data1").Address Then
       
    ray(1, 1) = "TEXT1 FR"
    ray(1, 2) = "TEXT1 NL"
    ray(2, 1) = "TEXT1 NL"
    ray(2, 2) = "TEXT1 FR"
    ray(3, 1) = "TEXT2 FR"
    ray(3, 2) = "TEXT2 NL"
    ray(4, 1) = "TEXT2 NL"
    ray(4, 2) = "TEXT2 FR"
    ray(5, 1) = "TEXT3 FR"
    ray(5, 2) = "TEXT3 NL"
    ray(6, 1) = "TEXT3 NL"
    ray(6, 2) = "TEXT3 FR"
    ray(7, 1) = "TEXT4 FR"
    ray(7, 2) = "TEXT4 NL"
    ray(8, 1) = "TEXT4 NL"
    ray(8, 2) = "TEXT4 FR"
    ray(9, 1) = "TEXT5 FR"
    ray(9, 2) = "TEXT5 NL"
    ray(10, 1) = "TEXT5 NL"
    ray(10, 2) = "TEXT5 FR"
    ray(11, 1) = "TEXT6 FR"
    ray(11, 2) = "TEXT6 NL"
    ray(12, 1) = "TEXT6 NL"
    ray(12, 2) = "TEXT6 FR"
    ray(13, 1) = "TEXT7 FR"
    ray(13, 2) = "TEXT7 NL"
    ray(14, 1) = "TEXT7 NL"
    ray(14, 2) = "TEXT7 FR"
    ray(15, 1) = "TEXT8 FR"
    ray(15, 2) = "TEXT8 NL"
    ray(16, 1) = "TEXT8 NL"
    ray(16, 2) = "TEXT8 FR"
    ray(17, 1) = "TEXT9 FR"
    ray(17, 2) = "TEXT9 NL"
    ray(18, 1) = "TEXT9 NL"
    ray(18, 2) = "TEXT9 FR"
    ray(19, 1) = "TEXT10 FR"
    ray(19, 2) = "TEXT10 NL"
    ray(20, 1) = "TEXT10 NL"
    ray(20, 2) = "TEXT10 FR"
    ray(21, 1) = "TEXT11 FR"
    ray(21, 2) = "TEXT11 NL"
    ray(22, 1) = "TEXT11 NL"
    ray(22, 2) = "TEXT11 FR"
    ray(23, 1) = "TEXT12 FR"
    ray(23, 2) = "TEXT12 NL"
    ray(24, 1) = "TEXT12 NL"
    ray(24, 2) = "TEXT12 FR"
    ray(25, 1) = "TEXT13 FR"
    ray(25, 2) = "TEXT13 NL"
    ray(26, 1) = "TEXT13 NL"
    ray(26, 2) = "TEXT13 FR"


       
       Txt = Range("Data2").Value
        For n = 1 To UBound(ray, 1)
            If ray(n, 1) = Txt Then
                Txt = ray(n, 2)
                Exit For
            End If
        Next n
       Select Case Target.Value
            Case "FR": nStr = "TEXT1 FR, TEXT2 FR, TEXT3 FR, TEXT4 FR, TEXT5 FR, TEXT6 FR, TEXT7 FR, TEXT8 FR, TEXT9 FR, TEXT10 FR, TEXT11 FR, TEXT12 FR, TEXT13 FR"
            Case "NL": nStr = "TEXT1 NL, TEXT2 NL, TEXT3 NL, TEXT4 NL, TEXT5 NL, TEXT6 NL, TEXT7 NL, TEXT8 NL, TEXT9 NL, TEXT10 NL, TEXT11 NL, TEXT12 NL, TEXT13 NL"
        End Select
 
    With Range("Data2").Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:=nStr
End With
 
Range("Data2").Value = Txt
       
       nray(1, 1) = "TEXT14 FR"
       nray(1, 2) = "TEXT14 NL"
       nray(2, 1) = "TEXT14 NL"
       nray(2, 2) = "TEXT14 FR"
       nray(3, 1) = "TEXT15 FR"
       nray(3, 2) = "TEXT15 NL"
       nray(4, 1) = "TEXT15 NL"
       nray(4, 2) = "TEXT15 FR"
        
       Txt = Range("Data3").Value
        For n = 1 To UBound(nray, 1)
            If ray(n, 1) = Txt Then
                Txt = ray(n, 2)
                Exit For
            End If
        Next n
       Select Case Target.Value
            Case "FR": nStr = "Please select, TEXT14 FR, TEXT15 FR"
            Case "NL": nStr = "Please select, TEXT14 NL, TEXT15 NL"
        End Select
 
    With Range("Data3").Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:=nStr
End With
 
Range("Data3").Value = Txt


    nray(1, 1) = "TEXT16 FR"
    nray(1, 2) = "TEXT16 NL"
    nray(2, 1) = "TEXT16 NL"
    nray(2, 2) = "TEXT16 FR"
    nray(3, 1) = "TEXT17 FR"
    nray(3, 2) = "TEXT17 NL"
    nray(4, 1) = "TEXT17 NL"
    nray(4, 2) = "TEXT17 FR"
    nray(5, 1) = "TEXT18 FR"
    nray(5, 2) = "TEXT18 NL"
    nray(6, 1) = "TEXT18 NL"
    nray(6, 2) = "TEXT18 FR"
    nray(7, 1) = "TEXT19 FR"
    nray(7, 2) = "TEXT19 NL"
    nray(8, 1) = "TEXT19 NL"
nray(8, 2) = "TEXT19 FR"


Txt = Range("Data4").Value


        For n = 1 To UBound(nray, 1)
            If nray(n, 1) = Txt Then
                Txt = nray(n, 2)
                Exit For
            End If
        Next n
        Select Case Target
            Case "FR": nStr = "Please select, TEXT16 FR, TEXT17 FR, TEXT18 FR, TEXT19 FR"
            Case "NL": nStr = "Please select, TEXT16 NL, TEXT17 NL, TEXT18 NL, TEXT19 NL"
        End Select


With Range("Data4").Validation
    .Delete
    .Add Type:=xlValidateList, Formula1:=nStr
End With


Range("Data4").Value = Txt


End If
Application.EnableEvents = True


   
End Sub
 
Last edited:
Upvote 0
Add the code below to your code just above the last "End If".
Once working you will obviously need to alter the word as per the format shown in this example:-
Don't forget to name this new range "X11" as "Data4".
Code: 
 ReDim sray(1 To 26, 1 To 2)   

    sray(1, 1) = "fr1"
    sray(1, 2) = "eng1"
    sray(2, 1) = "eng1"
    sray(2, 2) = "fr1"
    sray(3, 1) = "fr2"
    sray(3, 2) = "eng2"
    sray(4, 1) = "eng2"
    sray(4, 2) = "fr22"
    sray(5, 1) = "fr3"
    sray(5, 2) = "eng3"
    sray(6, 1) = "eng3"
    sray(6, 2) = "fr3"
    sray(7, 1) = "fr4"
    sray(7, 2) = "eng4"
    sray(8, 1) = "eng4"
    sray(8, 2) = "fr4"
    sray(9, 1) = "fr5"
    sray(9, 2) = "eng5"
    sray(10, 1) = "eng5"
    sray(10, 2) = "fr5"
    sray(11, 1) = "fr6"
    sray(11, 2) = "eng6"
    sray(12, 1) = "eng6"
    sray(12, 2) = "fr6"
    sray(13, 1) = "fr7"
    sray(13, 2) = "eng7"
    sray(14, 1) = "eng7"
    sray(14, 2) = "fr7"
    sray(15, 1) = "fr8"
    sray(15, 2) = "eng8"
    sray(16, 1) = "eng8"
    sray(16, 2) = "fr8"
    sray(17, 1) = "fr9"
    sray(17, 2) = "eng9"
    sray(18, 1) = "eng9"
    sray(18, 2) = "fr9"
    sray(19, 1) = "fr10"
    sray(19, 2) = "eng10"
    sray(20, 1) = "eng10"
    sray(20, 2) = "fr10"
    sray(21, 1) = "fr11"
    sray(21, 2) = "eng11"
    sray(22, 1) = "eng11"
    sray(22, 2) = "fr11"
    sray(23, 1) = "fr12"
    sray(23, 2) = "eng12"
    sray(24, 1) = "eng12"
    sray(24, 2) = "fr12"
    sray(25, 1) = "fr13"
    sray(25, 2) = "eng13"
    sray(26, 1) = "eng13"
    sray(26, 2) = "fr13"

Txt = Range("Data4").Value

        For n = 1 To UBound(sray, 1)
            If sray(n, 1) = Txt Then
                Txt = sray(n, 2)
                Exit For
            End If
        Next n
        Select Case Target
            Case "FR": nStr = "Please Select,fr1,fr2,fr3,fr4,fr5,fr6,fr7,fr8,fr9,fr10,fr11,fr12,fr13"
            Case "ENG": nStr = "Please Select,eng1,eng2,eng3,eng4,eng5,eng6,eng7,eng8,eng9,eng10,eng11,eng12,eng13"
        End Select

With Range("Data4").Validation
    .Delete
    .Add Type:=xlValidateList, Formula1:=nStr
End With
Range("Data4").Value = Txt
 
Upvote 0
ok works fine !
Thank you again.

Is it possible to select a value from a cell in another sheets, instead of typing the value in the code ?
This would be much more flexible (we update our list regularly).

Example :
value "fr1" = "Sheet2!A49"
value "eng1" = "Sheets2!B49"

of even beter

value "fr1" = "Data5" (in Sheet 2)
value "eng1" = "Data6" (in Sheet 2)

Sure this would be very easy...
 
Upvote 0
From now my code looks like this :

Code: 
Dim ray As Variant, n As Long, ac As Long, Txt As String, nStr As StringApplication.EnableEvents = False
ReDim ray(1 To 4, 1 To 2)
ReDim nray(1 To 8, 1 To 2)
If Target.Address = Range("Data1").Address Then
       ray(1, 1) = "fr14"
       ray(1, 2) = "eng14"
       ray(2, 1) = "eng14"
       ray(2, 2) = "fr14"
       ray(3, 1) = "fr15"
       ray(3, 2) = "eng15"
       ray(4, 1) = "eng15"
       ray(4, 2) = "fr15"
        
       Txt = Range("Data2").Value
        For n = 1 To UBound(ray, 1)
            If ray(n, 1) = Txt Then
                Txt = ray(n, 2)
                Exit For
            End If
        Next n
       Select Case Target.Value
            Case "FR": nStr = "Please Select,fr14,fr15"
            Case "NL": nStr = "Please Select,eng14,eng15"
        End Select
 
    With Range("Data2").Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:=nStr
End With
 
Range("Data2").Value = Txt


    nray(1, 1) = "fr16"
    nray(1, 2) = "eng16"
    nray(2, 1) = "eng16"
    nray(2, 2) = "fr16"
    nray(3, 1) = "fr17"
    nray(3, 2) = "eng17"
    nray(4, 1) = "eng17"
    nray(4, 2) = "fr17"
    nray(5, 1) = "fr18"
    nray(5, 2) = "eng18"
    nray(6, 1) = "eng18"
    nray(6, 2) = "fr18"
    nray(7, 1) = "fr19"
    nray(7, 2) = "eng19"
    nray(8, 1) = "eng19"
nray(8, 2) = "fr19"




Txt = Range("Data3").Value


        For n = 1 To UBound(nray, 1)
            If nray(n, 1) = Txt Then
                Txt = nray(n, 2)
                Exit For
            End If
        Next n
        Select Case Target
            Case "FR": nStr = "Please Select,fr16,fr17,fr18,fr19"
            Case "NL": nStr = "Please Select,eng16,eng17,eng18,eng19"
        End Select


With Range("Data3").Validation
    .Delete
    .Add Type:=xlValidateList, Formula1:=nStr
End With


Range("Data3").Value = Txt


 ReDim sray(1 To 26, 1 To 2)


    sray(1, 1) = "fr1"
    sray(1, 2) = "eng1"
    sray(2, 1) = "eng1"
    sray(2, 2) = "fr1"
    sray(3, 1) = "fr2"
    sray(3, 2) = "eng2"
    sray(4, 1) = "eng2"
    sray(4, 2) = "fr22"
    sray(5, 1) = "fr3"
    sray(5, 2) = "eng3"
    sray(6, 1) = "eng3"
    sray(6, 2) = "fr3"
    sray(7, 1) = "fr4"
    sray(7, 2) = "eng4"
    sray(8, 1) = "eng4"
    sray(8, 2) = "fr4"
    sray(9, 1) = "fr5"
    sray(9, 2) = "eng5"
    sray(10, 1) = "eng5"
    sray(10, 2) = "fr5"
    sray(11, 1) = "fr6"
    sray(11, 2) = "eng6"
    sray(12, 1) = "eng6"
    sray(12, 2) = "fr6"
    sray(13, 1) = "fr7"
    sray(13, 2) = "eng7"
    sray(14, 1) = "eng7"
    sray(14, 2) = "fr7"
    sray(15, 1) = "fr8"
    sray(15, 2) = "eng8"
    sray(16, 1) = "eng8"
    sray(16, 2) = "fr8"
    sray(17, 1) = "fr9"
    sray(17, 2) = "eng9"
    sray(18, 1) = "eng9"
    sray(18, 2) = "fr9"
    sray(19, 1) = "fr10"
    sray(19, 2) = "eng10"
    sray(20, 1) = "eng10"
    sray(20, 2) = "fr10"
    sray(21, 1) = "fr11"
    sray(21, 2) = "eng11"
    sray(22, 1) = "eng11"
    sray(22, 2) = "fr11"
    sray(23, 1) = "fr12"
    sray(23, 2) = "eng12"
    sray(24, 1) = "eng12"
    sray(24, 2) = "fr12"
    sray(25, 1) = "fr13"
    sray(25, 2) = "eng13"
    sray(26, 1) = "eng13"
    sray(26, 2) = "fr13"


Txt = Range("Data4").Value


        For n = 1 To UBound(sray, 1)
            If sray(n, 1) = Txt Then
                Txt = sray(n, 2)
                Exit For
            End If
        Next n
        Select Case Target
            Case "FR": nStr = "Please Select,fr1,fr2,fr3,fr4,fr5,fr6,fr7,fr8,fr9,fr10,fr11,fr12,fr13"
            Case "NL": nStr = "Please Select,eng1,eng2,eng3,eng4,eng5,eng6,eng7,eng8,eng9,eng10,eng11,eng12,eng13"
        End Select


With Range("Data4").Validation
    .Delete
    .Add Type:=xlValidateList, Formula1:=nStr
End With
Range("Data4").Value = Txt


End If
Application.EnableEvents = True


   
End Sub
 
Upvote 0
Another problem : when I try a name (ex : I change "fr1" in "example1") an that I try another name after (ex : I change "example1" in "example2") => then "example1 stay on my dropdown list + the other dropdown lists do not work anymore (no switching languages). Is there a way to 'reset' ?
 
Upvote 0
Perfect for the new button 'reset' !!

In my (numerous) previous attempts, I have the impression that I was able at one time to replace the values of the choices (ex : fr1, fr2 etc.) by a value of a cell which was on the same sheet (i did tried first on another sheet but seems to difficult so I gave up the idea).

Ex:
sray(1, 1) = "fr1"
sray(1, 2) = "eng1"
sray(2, 1) = "eng1"
sray(2, 2) = "fr1"

Become :

sray(1, 1) = Cells(24, 190)
sray(1, 2) = Cells(24, 191)
sray(2, 1) = Cells(24, 191)
sray(2, 2) = Cells(24, 190)


But I can not repeat this, and find how I could do that...

If to complicated, I will input my names manually in the code (and change it manually at every update).
 
Upvote 0
Do you confirm that it is not possible to get easely value from a cell (in the same worksheets) to input in the code ?
Thanks.
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
run VBA when clicked
Replies
4
Views
95
Gylle
G
D
  • Question Question
Populate dropdown list of values from table based on cell value
Replies
4
Views
2K
duggie33
D
M
  • Question Question
Making cells have the same behaviour as control textbox based on dropdown list
Replies
0
Views
731
maxipoblete
M
O
  • Solved
Multiple Dropdown lists (sheets) default to Public Variable
Replies
6
Views
1K
Fluff
Fluff
S
  • Solved
Apply the format of a cell referenced in a data validation dropdown list.
Replies
8
Views
5K
scootty83
S

Forum statistics

Threads
1,223,275
Messages
6,171,122
Members
452,381
Latest member
Nova88

Share this page

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