Excel VBA UserForm: Populating Combobox 2 based on selected value in Combobox 1

[NewbieNils]

I have created a userform with two comboboxes, based on combobox 1 I would like to populate combobox two.

In combobox 1 there are 6 items to choose from (populated from a range in sheet “Production”, itemA, itemB, ItemC, ItemD, ItemE, ItemF

Based on the selected item in Combobox1 I would like to populate combobox2 as following:

If ItemA is selected, Combobox2 should be populated from range in Sheet ”supply_to_production” range (T2:T (till last filled cell))

If ItemB is selected, Combobox2 should be populated from range in Sheet ”supply_to_production” range (V2:V (till last filled cell))

If ItemC is selected, Combobox2 should be populated from range in Sheet ”supply_to_production” range (X2:X (till last filled cell))

If ItemD is selected, Combobox2 should be populated from range in Sheet ”supply_to_production” range (Z2:Z (till last filled cell))

If ItemE is selected, Combobox2 should be populated from range in Sheet ”supply_to_production” range (AB2:AB (till last filled cell))

If ItemF is selected, Combobox2 should be populated from range in Sheet ”supply_to_production” range (AD2:AD (till last filled cell))

Honestly saying, I have no idea where to start, some help would be much appreciated.

 

[smozgur]

Welcome to the MrExcel Message Board!

Please double-click on the ComboBox1 on the user form in design mode, and make sure to use the following code in the ComboBox1_Change event procedure.
I wrote comments to explain how it works, but please let us know if you have any questions.

Private Sub ComboBox1_Change()
Dim sht As Worksheet
Dim rng As Range

    ' Reset the Combobox2 selection, if any
    ComboBox2.ListIndex = -1

    ' Set the source worksheet
    Set sht = ThisWorkbook.Worksheets("supply_to_production")

    ' Get the list column reference according to the selected item index
    Set rng = sht.Range("T2").Offset(, ComboBox1.ListIndex * 2)
    
    ' Get the list values
    Set rng = sht.Range(rng.Cells(1, 1), rng.Cells(1, 1).End(xlDown))
    
    ' Set the ComboBox2 options dynamically by using the range we found in the previous step
    ComboBox2.RowSource = "'" & sht.Name & "'!" & rng.Address()
End Sub

The previous code assumes that list value columns start from column T, next list column is the third column starting from the previous column (there is a column between each list columns). So, I used the selected item index starting from 0 to the end of the list, and calculated the offset column numbers starting from column T.

If ItemA is selected, then ComboBox1.ListIndex value = 0, offset value = 2 x 0 = 0, T column is used.
If ItemB is selected, then ComboBox1.ListIndex value = 1, offset value = 2 x 1 = 2, V column is used.
and so on..

However, this might not be possible all the time, and the list columns might be located somewhere else. In this case, when there is not a certain pattern that will help us to find the list columns easily then we can simply select the reference column cell according to the name of the selected item. In this case, the following code could be used.
The only difference is the rng setting method, but I wanted to post this as a separate code snippet to avoid any confusion.

Private Sub ComboBox1_Change()
Dim sht As Worksheet
Dim rng As Range

    ' Reset the Combobox2 selection, if any
    ComboBox2.ListIndex = -1

    ' Set the source worksheet
    Set sht = ThisWorkbook.Worksheets("supply_to_production")

    ' Get the list column reference according to the selected item value
    Select Case ComboBox1.Value
        Case "ItemA"
            Set rng = sht.Range("T2")
        Case "ItemB"
            Set rng = sht.Range("V2")
        Case "ItemC"
            Set rng = sht.Range("X2")
        Case "ItemD"
            Set rng = sht.Range("Z2")
        Case "ItemE"
            Set rng = sht.Range("AB2")
        Case "ItemF"
            Set rng = sht.Range("AD2")
    End Select
    
    ' Get the list values
    Set rng = sht.Range(rng.Cells(1, 1), rng.Cells(1, 1).End(xlDown))
    
    ' Set the ComboBox2 options dynamically by using the range we found in the previous step
    ComboBox2.RowSource = "'" & sht.Name & "'!" & rng.Address()
End Sub

 

[dmt32]

Hi,
another way maybe

Private Sub ComboBox1_Change()
    Dim rng         As Range
    Dim i           As Long
    
    i = Me.ComboBox1.ListIndex + 1
    Me.ComboBox2.Clear
    If i = 0 Then Exit Sub
    
    With ThisWorkbook.Worksheets("supply_to_production")
        Set rng = .Range("T2,V2,X2,Z2,AB2,AD2")
        Set rng = rng.Areas(i).Resize(.Cells(.Rows.Count, rng.Areas(i).Column).End(xlUp).Row - 1)
    End With
    
    Me.ComboBox2.List = rng.Value
    
End Sub

Dave