linking two comboboxes

  • Hi,
    I want to use 2 comboboxes on a sheet where the value of cbo2 depends on the selection in cbo1.
    ie when I choose fruit in cbo1 I'll only get banana and pineapple in cbo2.
    I know how I can create it with the datavalidation but not with VBA code.
    Can someone helpme with the code please?


  • Hi guys,

    Yes, that was a looong way :wink1:

    Here is short sample which need to be adjusted to fit Your terms:

    Option Explicit

    Private Sub UserForm_Initialize()
    Dim vaLista1 As Variant
    vaLista1 = VBA.Array("Januari", "Februari", "Mars", "April")

    With ListBox1
    .List = vaLista1
    End With
    End Sub

    Private Sub ListBox1_Click()
    Dim vaJan, vaFeb, VaMar, vaApril As Variant

    vaJan = VBA.Array("1", "2", "3")
    vaFeb = VBA.Array("10", "20", "30")
    VaMar = VBA.Array("100", "200", "300")
    vaApril = VBA.Array("1000", "2000", "3000")

    Select Case ListBox1.Value
    Case "Januari"
    ListBox2.List = vaJan
    Case "Februari"
    ListBox2.List = vaFeb
    Case "Mars"
    ListBox2.List = VaMar
    Case "April"
    ListBox2.List = vaApril
    End Select
    End Sub

    Private Sub cmbOK_Click()
    Dim i As Integer
    Dim rnCell As Range

    Set rnCell = Sheets("ListBox4").Range("A1")

    If ListBox2.ListIndex = -1 Then
    MsgBox "No option selected!", vbCritical
    Exit Sub
    End If

    rnCell.Value = ListBox2.Value

    Unload Me
    End Sub

    Play around with it so You understand it then implement it.

  • Thanx XL-Dennis,
    But I'm not smart enough to understand the code.
    Can you give me a file where the code is used in? Then it's easier to understand.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!