PDA

View Full Version : Making 2 Dependent drop downs



kssandhya
12-15-2019, 11:32 PM
Sub Test()
'drop down list
Dim language As Variant
Dim team As Variant

language = [{"KA", "KT", "PJ", "TU"}] ' assign value
'put in K2 array language
With Range("K2").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=Join(language, ",")
End With

team = [{"Team 1", "Team 2", "Team 3"}] ' assign value
'put in J2 array team
With Range("J2").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=Join(team, ",")
End With
End Sub


When I click on Team 1 it should KA, KT and when I click on Team 2 it should show PJ and when I click on Team TU only. Currently they are just 2 drop downs want to make them dependent drop downs.

If team = "Team1" Then
language= "KA,KT"
ElseIf team = "Team2" Then
language= "GU"
ElseIf team = "Team3" Then
language= "KA"
Else
End If

The data for both the drop down should not come from excel sheet. I want to hard core the code with the drop down values and make them dependable drop downs.




Any help is much appreciated.

KOKOSEK
12-21-2019, 02:46 AM
So depends what you choose in J2 dropdown list in K2 have to have other choices, right?

Put into sheet code (not a standard module):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim language As Variant
If Not Intersect(Target, [J2]) Is Nothing Then
Select Case Target
Case "Team 1"
language = [{"KA", "KT"}] ' assign value
Case "Team 2"
language = [{"GU"}] ' assign value
Case "Team 3"
language = [{"KA"}] ' assign value
End Select
With Range("K2").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Join(language, ",")
End With
End If
End Sub




not tested.