Making 2 Dependent drop downs

[kssandhya]

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]

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.