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

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
And last question : I have already another private sub in my document (other function - same worksheet)
and I got an error, how can I manage to have 2 private sub ?
thanks !
 
Upvote 0
Re Post #12. I imagine there is a problem with the code"

Re Post 9,10
Try this:-
You will need to name the 3 cells "X7, X26, X37" as "Data1", "Data2" & "Data3"
Then load the code below:-
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
ReDim ray(1 To 4, 1 To 2)
ReDim nray(1 To 8, 1 To 2)
[COLOR="Navy"]If[/COLOR] Target.Address = Range("Data1").Address [COLOR="Navy"]Then[/COLOR]
       ray(1, 1) = "Avion"
       ray(1, 2) = "Airoplane"
       ray(2, 1) = "Airoplane"
       ray(2, 2) = "Avion"
       ray(3, 1) = "Maison"
       ray(3, 2) = "House"
       ray(4, 1) = "House"
       ray(4, 2) = "Maison"
        
       Txt = Range("Data2").Value
        [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
       Select Case Target.value
            [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("Data2").Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:=nStr
[COLOR="Navy"]End[/COLOR] With
 
Range("Data2").Value = Txt

    nray(1, 1) = "musique"
    nray(1, 2) = "music"
    nray(2, 1) = "music"
    nray(2, 2) = "musique"
    nray(3, 1) = "université"
    nray(3, 2) = "university"
    nray(4, 1) = "university"
    nray(4, 2) = "université"
    nray(5, 1) = "rectangulaire"
    nray(5, 2) = "rectangular"
    nray(6, 1) = "rectangular"
    nray(6, 2) = "rectangulaire"
    nray(7, 1) = "fourniture"
    nray(7, 2) = "furniture"
    nray(8, 1) = "furniture"
nray(8, 2) = "fourniture"

'[COLOR="Green"][B]musique/université/rectangulaire/fourniture (fr) OR music/university/rectangular/furniture (eng)[/B][/COLOR]
Txt = Range("Data3").Value

        [COLOR="Navy"]For[/COLOR] n = 1 To UBound(ray, 1)
            [COLOR="Navy"]If[/COLOR] nray(n, 1) = Txt [COLOR="Navy"]Then[/COLOR]
                Txt = nray(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 = "musique,université,rectangulaire,fourniture"
            [COLOR="Navy"]Case[/COLOR] "ENG": nStr = "music,university,rectangular,furniture"
        [COLOR="Navy"]End[/COLOR] Select

[COLOR="Navy"]With[/COLOR] Range("Data3").Validation
    .Delete
    .Add Type:=xlValidateList, Formula1:=nStr
[COLOR="Navy"]End[/COLOR] With

Range("Data3").Value = Txt
[COLOR="Navy"]End[/COLOR] If
Application.EnableEvents = True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Last edited:
Upvote 0
aie, not working.
I'm sure we are very close.
No message error, I tried in a new blank excel also, and I changed de cell names...

Approach followed :
1. Open new sheet
2. Change name X7 (in 'Data1'), X26 (in 'Data2') and X37 (in 'Data3')
2. Install dropdown list in X7 (choice FR or ENG)
3. Right-clic on sheet : view code : double-clic sheet1 : paste code (#13) : close editor
4. Try to change FR or ENG in X7
=> nothing happens

Did I miss a step?
(No more questions after that :))

For information, the other active code in my sheet is (code to automatically hide rows):


Code: 
Private Sub Worksheet_Change(ByVal Target As Range)   If Not Intersect(Target, Range("X2:X6")) Is Nothing Then
      Cells.EntireRow.Hidden = False
      Select Case Range("X2")
         Case "TEXT1"
            Range("17:17,20:20,30:50,104:105,112:126,138:138").EntireRow.Hidden = True
         Case "TEXT2"
            Range("17:17,20:20,30:50,112:126,138:138").EntireRow.Hidden = True
        Case "TEXT3"
            Range("17:17,19:19,30:50,112:126").EntireRow.Hidden = True
        Case "TEXT4"
            Range("17:17,20:20,30:50,106:107,112:126,138:138").EntireRow.Hidden = True
        Case "TEXT5"
            Range("17:17,19:19,30:50,112:126,138:138").EntireRow.Hidden = True
        Case "TEXT6"
            Range("17:17,30:50,106:107,112:126,138:138").EntireRow.Hidden = True
        Case "TEXT7"
            Range("17:17:30:31").EntireRow.Hidden = True
        Case "TEXT8"
            Range("17:17:30:31").EntireRow.Hidden = True
        Case "TEXT9"
            Range("17:17:30:31").EntireRow.Hidden = True
        Case "TEXT10"
            Range("17:17,19:19,23:23,25:25,30:50,52:149").EntireRow.Hidden = True
        Case "TEXT11"
            Range("17:17,19:19,23:23,25:25,31:31,34:34,36:38,41:45,57:58,61:77,84:84,90:91,93:97,104:107,109:109,139:148").EntireRow.Hidden = True
        Case "TEXT12"
            Range("17:17,19:20,23:23,25:25,30:50,57:58,61:61,68:68,70:70,72:74,77:77,84:84,90:91,93:97,104:105,112:126,139:148").EntireRow.Hidden = True
        Case "TEXT13"
            Range("17:17,19:20,23:23,25:25,31:50,57:58,61:61,68:68,70:70,72:74,77:77,84:84,90:91,93:97,104:105,112:126,139:148").EntireRow.Hidden = True
      End Select
      
      
      Select Case Range("X3")
         Case "TEXT14"
            Range("60:61,87:88,90:92,106:106,109:109").EntireRow.Hidden = True
      End Select
     
     
     Select Case Range("X4")
         Case "TEXT15"
            Range("57:57,58:58,61:61,68:68,70:70,72:74,77:77,84:84,93:97,104:105,123:126,139:148").EntireRow.Hidden = True
        Case "TEXT16"
            Range("122:122,128:138").EntireRow.Hidden = True
      End Select
      
           Select Case Range("X5")
         Case "TEXT17"
            Range("62:77").EntireRow.Hidden = True
        Case "TEXT18"
            Range("53:61").EntireRow.Hidden = True
      End Select
      
           Select Case Range("X6")
         Case "TEXT19"
            Range("52:149").EntireRow.Hidden = True
        Case "TEXT20"
            Range("53:98,102:103,106:110,112:149").EntireRow.Hidden = True
      End Select
      
   End If
End Sub

(I still have to name the rows to be more flexible)
 
Last edited:
Upvote 0
You can't have 2 "Change_Events" in the same sheet!!!
Try pasting my code below yours in your Event Code. !!
I note in some cases in your code, the validation cells in my code are Hidden !!!
 
Upvote 0
ok sorry, I'm discovering...Got it now !
It's works fine BUT not for the choice 'rectangular' and 'furniture' in Data3 => they don't switch languages (but OK for music and university).
Indeed, some of my code hide some of your validation cells (depent of choice in X2 and others), that is wanted.
 
Upvote 0
If the red line below in your code it is "Ubound(ray,1)", but it should read "Ubound(nRay,1)" as below.
Code: 
'musique/université/rectangulaire/fourniture (fr) OR music/university/rectangular/furniture (eng)
Txt = Range("Data3").Value

        For n = 1 To[COLOR="#FF0000"][SIZE=4][B] UBound(nray, 1)
[/B][/SIZE][/COLOR]            If nray(n, 1) = Txt Then
                Txt = nray(n, 2)
                Exit For
            End If
        Next n
 
Upvote 0
Super, everything is working !
Thanks again, very impressive both technically and personally (to help me as an unknown people). All the best MickG!
(I'm really curious about who are these people are give time to others)
 
Upvote 0
I would like to add an option in the dropdown list :
- Data2 : 'Please Select'
- Data3 : 'Please Select'
(not to be necessary switchable in language, no so important)
I tried but not succeded...
(very last adaptation).
 
Last edited:
Upvote 0
Perhaps like this in both "Select cases "

Code: 
Select Case Target.Value
            Case "FR": nStr = "Please Select,Avion,Maison"
            Case "ENG": nStr = "Please Select,House,Airoplane"
        End Select

Select Case Target.Value
            Case "FR": nStr = "Please Select,musique,université,rectangulaire,fourniture"
            Case "ENG": nStr = "Please Select,music,university,rectangular,furniture"
        End Select
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
run VBA when clicked
Replies
4
Views
116
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
733
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,893
Messages
6,175,246
Members
452,623
Latest member
cliftonhandyman

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