VBA code for creating dependent drop down list

Do you know you can do this without VBA?

There's some info here: http://www.contextures.com/xldataval02.html

Dom

Just thought I should point out that it can be done without it.

Have a look here: http://www.xtremevbtalk.com/showpost...22&postcount=5

Dom

Originally Posted by singhabhijitkumar

Hi, thanks. I have two problems with this. Firstly, is that I am unable to modify the named list, or delete it. Secondly, the values don't get resetted to blank if i change my parent value in drop down list. For exampl I have selected FRUIT in my parent dropdown and APPLE in my dependent dropdown. Now if I change FRUIT to VEGETABLE, the dependant cell still shows APPLE. I want it to be resetted to blank.

Please could you help

use

Private Sub ComboBox1_change()
    Dim t As Range
    Dim i As Integer
    Dim ws As Worksheet
    Set ws = ActiveWorkbook.Worksheets("Sheet1")
    
    ComboBox1.ShowDropButtonWhen = fmShowDropButtonWhenAlways
        
    If ComboBox1.Value = "" Then ComboBox2.Clear
    If ComboBox1.ListIndex > 0 Then ComboBox2.Clear
        If ComboBox1.ListIndex = 0 Then
            ComboBox2.Clear
            ComboBox2.Value = ""
        ElseIf ComboBox1.ListIndex = 1 Then
            ComboBox2.Clear
            For Each tower In ws.Range("Range1")
                With Me.ComboBox2
                    .AddItem t.Value
                    .List(.ListCount - 1, 1) = t.Offset(0, 1).Value
                End With
            Next t
        ElseIf ComboBox1.ListIndex = 2 Then
            ComboBox2.Clear
            For Each tower In ws.Range("Range2")
                With Me.ComboBox2
                    .AddItem t.Value
                    .List(.ListCount - 1, 1) = t.Offset(0, 1).Value
                End With
            Next t
        ElseIf ComboBox1.ListIndex = 3 Then
           ComboBox2.Clear
            For Each tower In ws.Range("Range3")
                With Me.ComboBox2
                    .AddItem t.Value
                    .List(.ListCount - 1, 1) = t.Offset(0, 1).Value
                End With
            Next t
        End If
End Sub

Hope this helps...

Follow the link below to find the related post...

http://www.excelforum.com/excel-prog...ist-boxes.html

Its easy to follow and helps alot...


Small suggestion here, if u want to add small data use below code..

 With ComboBox2
            .List = Array("Japan", "China", "Korea", "Taiwan", "Aus/ANZ", "Hong Kong", "Singapore")
        End With

otherwise, if u want to add data from the spreadsheet range use below for loop to add data to the combo box

        For Each tower In ws.Range("TowerNum_Type1r")
            With Me.cmbTowerNumber
                .AddItem tower.Value
                .List(.ListCount - 1, 1) = tower.Offset(0, 1).Value
            End With
        Next tower

Regards,

SK