How to make the Combobox item list dynamic?

Question

I am using a 'Generate' button on my worksheet. When I click on the button, a popup (form) come, which contains two comboboxes. Basis the selection in the first combobox, the second combobox option list is populated.

For the first combobox, when I hardcode the item values it works fine. The form code is as follows:

Private Sub UserForm_Initialize()

With ComboBox_DL
    .AddItem "DL1"
    .AddItem "DL2"
End With

End Sub

I tried to make this item list dynamic by fetching the combobox item values from a column the in the excel worksheet using the following form code:

Private Sub UserForm_Initialize()

With ComboBox_DL
For Each c In ActiveSheet.Range(Range("AE"), Range("AE").End(xlDown))
 .AddItem c.Value
Next
End With

End Sub

But the above code throws error: Run time error '1004': Method 'Range' of object '_Global' failed

I modified the code adding sheet details: 
With ComboBox_DL
    For Each c In ThisWorkbook.Worksheets("Business_Input_Data").Range(Range("AE"), Range("AE").End(xlDown))
        .AddItem c.Value
Next

It still throws the same error.

Can someone help please? Also, I want to know how to look up the values corresponding to the selection in combobox1 and populate the list in combobox2?

Answer 1

If your combobox entries are a list on a worksheet, you don't need to use VBA to fill them at all. Instead, you can create a Dynamic Named Range, and use that as the Rowsource for the combobox.

Say your list starts on Sheet3, cell A1. Go to Formulas | Name Manager to create a named range. Give it a useful name like "Combo", then put the following formula into RefersTo: =OFFSET(Sheet3!$A$1,0,0,COUNTA(Sheet3!$A:$A),1) Save and close the Named Ranges dialogue.

In the properties of your combobox, look for the line "RowSource". Set it to =Combo, or whatever name you used for your named range.

Any changes to the list, including lengthening or shortening it, will now be reflected immediately and automatically in the combo box.

EDITED TO ADD:

To use the value selected in the first combobox to determine what list is used in a second combobox, we'll need to do two things.

The first is to create named ranges for all the possible selections in the first list:

In the image, column A is the source for our first combo box; the other columns contain the possible sources for the second combo box.

We then just need to put a little bit of code in the Change event for the first combobox:

Private Sub ComboBox1_Change()
    Me.ComboBox2.Value = ""
    Me.ComboBox2.RowSource = "=" & Me.ComboBox1.Value
End Sub

This code will trigger whenever ComboBox1 is changed. First it clears any existing value in ComboBox2, then it will set the row source property of ComboBox2 to a combination of the = symbol and whatever value was selected in the first box. Since those values are also named ranges, the second box will now use the selected named range as its list source.

If you needed to, you could add more levels of cascading options, with different named ranges for each one. More than a couple of levels may become unmanageable, though - at which point we may want to look at another method.

Answer 2

Havent tested this as i have not created the userform to test under same conditions, but should work subject to minor alterations.

Dim n As Long
n = Sheets("Business_Input_Data").Cells(Rows.Count, "AE").End(xlUp).Row

With ComboBox_DL
    For Each c In ThisWorkbook.Worksheets("Business_Input_Data").Range("AE" & n)
        .AddItem c.Value
Next

Answer 3

you're missing the row index in "AE" furthermore use always explicit worksheet qualification in any Range reference

Private Sub UserForm_Initialize()
    Dim c As Range

    With ComboBox_DL
        For Each c In For Each c In ThisWorkbook.Worksheets("Business_Input_Data").Range(ThisWorkbook.Worksheets("Business_Input_Data").Range("AE1"), ThisWorkbook.Worksheets("Business_Input_Data").Range("AE1").End(xlDown))
         .AddItem c.Value
        Next
    End With
End Sub

but more elegant solutions are:

Private Sub UserForm_Initialize()        
    With ThisWorkbook.Worksheets("Business_Input_Data")
        ComboBox_DL.RowSource = .Range("AE1", .Range("AE1").End(xlDown)).Address
    End With        
End Sub


Private Sub UserForm_Initialize()        
    With ThisWorkbook.Worksheets("Business_Input_Data")
        ComboBox_DL.List = .Range("AE1", .Range("AE1").End(xlDown)).Value
    End With        
End Sub

where:

• the former binds your ComboBox list to the range values whose address is given as ComboBox RowSource property

• the latter takes the values of the given range as Combobox values

Answer 4

This is a solution to dynamically update the comboBox with emails in one column range.

    Dim c As Range

ComboBox1.Value = ""
ComboBox1.Clear
For Each c In Sheets("emails").Range("F5:F5000")
    If c Like "*@*" Then
        ComboBox1.AddItem c
    End If
Next

The 'ComboBox.Value' Set the initial value. The 'ComboBox.Clear' Clears the previous rows in the comboBox.