Thread: Dynamic dropdown list selection based on combo box

Hi there,

I am trying to understand how the Replace() part works. Operationally, it is a dynamic dropdown list based on combo boxes on a user form.

Private Sub ComboBox1_Change()
Dim strRange As String
    If ComboBox1.ListIndex > -1 Then
       strRange = ComboBox1
       Label2.Caption = strRange
       strRange = Replace(strRange, " ", "_")
            With ComboBox2
                .RowSource = vbNullString
                .RowSource = strRange
                .ListIndex = 0
            End With
    Else
       Label2.Caption = "Associated Items"
    End If
End Sub

How does the replace() part work? and what does the .listindex > -1 represent?

thanks

ListIndex returns the item that is selected (0 is the first item, 1 the second and so on) or -1 if no item has been chosen.

The data for the list is coming from named ranges, which cannot have spaces in the name, so they have been named using _ instead. The combobox contains the names with spaces, the replace changes those for _ to match the actual names and then assigns that to the list.

Have not looked at the file, but if you had say 'Named Range 1' in the list, the actual range name would be 'Named_Range_1' and that is what the Replace function converts to.

You can, but the point of this is that it will automatically expand as you add data in column C.
It basically says start from C2 and then use as many rows as have data in C2:C100.

Yes, as long as there is something in C1 on the sheet. My preference would actually be for

=Sheet1!$C$2:INDEX(Sheet1!$C$:$C$,COUNTA(Sheet1!$C$:$C$))