davesexcel.com

Excel information

Dependent ComboBox

Populate ComboBox2 based on ComboBox1 selection

Create the list




Select the Deveoper Ribbon then Insert and
Select ComboBox from the
Activex Controls



Draw the ComboBoxes onto the Sheet



Create Range to Place Results of ComboBoxes, this Example used F16


This is what the Worksheet looks like



Right click on the Sheet tab and select View Code




Copy and Paste this code


'----------------------------------------------------------
Dim rng As Range, c As Range, r As Range, f As Range
Dim rws As Long, y As Integer



Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Count > 1 Then Exit Sub
    If Target.Address <> "$C$1" Then Exit Sub


    rws = ActiveSheet.UsedRange.Columns(1).Rows.Count


    ComboBox1.Clear

    Set r = Range(Cells(2, 1), Cells(rws, 1))

    For Each c In r.Cells

        y = Application.WorksheetFunction.CountIf(Range(Cells(1, 1), Cells(c.Row, 1)), c)

        If y = 1 Then ComboBox1.AddItem c

    Next c

    With ComboBox1

        .Activate
        .Application.SendKeys ("%{down}")

    End With


End Sub
Private Sub ComboBox1_Change()


    rws = ActiveSheet.UsedRange.Columns(1).Rows.Count

    ComboBox2.Clear

    Set r = Range(Cells(1, 1), Cells(rws, 1))

    For Each c In r.Cells


        If c = ComboBox1 Then ComboBox2.AddItem c.Offset(0, 1)

    Next c

    With ComboBox2

        .Activate
        .Application.SendKeys ("%{down}")

    End With


End Sub
Private Sub ComboBox2_Change()

    Set f = Range("F16")
    f = ComboBox1 & " " & ComboBox2
    f.Select
End Sub



Select C1 to reset ComboBox1



Make a selection from ComboBox1 to Populate ComboBox2



Select from ComboBox2 to populate F16




Download the Workbook

Dependant ComboBox.xlsm

The code will work for xl'03 and older, the download will not though. Follow the same example and select ComboBox from the Controls toolbar for earlier versions