Combobox (change event) dependent on Cell Range using VBA

Question

I have a Userform in which two Comboboxes with names "Combobox1" & "Combobox2". In my Excel sheet Name "Sheet1" I have Column A & B which have some values in Groups.

Aim: I want to Show the Combobox2 values as Column B values but it should be dependent on Column A values upon selection. I tried the below Code but not understanding where i am going wrong.

Private Sub Combobox1_Change()
    'interlink dropdown box
    Dim index As Integer
    index = Combobox1.ListIndex

    Select Case index
    Case Is = 0
            With Combobox2
                 Me.Combobox2.Clear
                 Worksheets("Sheet1").Range("B11:B13").Value

            End With
    Case Is = 1
            With Combobox2
                Me.Combobox2.Clear
                Worksheets("Sheet1").Range("B14:B16").Value

            End With
    Case Is = 2
            With Combobox2
                Me.Combobox2.Clear
                Worksheets("Sheet1").Range("B17:B19").Value
            End With
    End Select
End Sub


Private Sub UserForm_Initialize()    
    Me.Combobox1.AddItem "1"
    Me.Combobox1.AddItem "2"
    Me.Combobox1.AddItem "3"    
End Sub

Required Output:

z32a7ul z32a7ul · Accepted Answer · 2019-07-16T11:06:29

You have to add the items like you do in UserForm_Initialize. Furthermore, I recommend using a helper function for iterating the elements (AddItemsToComboBox):

Option Explicit

Private Sub Combobox1_Change()
    Dim varCombox2Items As Variant
    Select Case ComboBox1.Value
    Case "1"
        AddItemsToComboBox ComboBox2, Worksheets("Sheet1").Range("B11:B13").Value
    Case "2"
        AddItemsToComboBox ComboBox2, Worksheets("Sheet1").Range("B14:B16").Value
    Case "3"
        AddItemsToComboBox ComboBox2, Worksheets("Sheet1").Range("B17:B19").Value
    Case Else
        AddItemsToComboBox ComboBox2, Array("Ivalid Value in ComboBox1")
    End Select
End Sub

Private Sub UserForm_Initialize()
    AddItemsToComboBox ComboBox1, Array("1", "2", "3")
End Sub

Private Sub AddItemsToComboBox(cobTarget As MSForms.ComboBox, varItems As Variant, Optional blnClear As Boolean = True)
    If blnClear Then
        cobTarget.Clear
    End If
    Dim varItem As Variant: For Each varItem In varItems
        cobTarget.AddItem CStr(varItem)
    Next varItem
End Sub

Combobox (change event) dependent on Cell Range using VBA

1 Answers