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?


    Thanx,
    Filip

  • 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
    .Clear
    .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.


    Fluppe