ActiveX ComboBox dynamic range for ListFillRange

Question

I have ThisWorkbook.Sheets("MAIN") and ActiveX ComboBox11 on it. I also have table:

         A                    B 
1      John 1               10000
2      John 2               20000
3      John 3               20000
4      John 4               10000
5      John 5               50000
6      John 6               50000
7      John 7               50000
8      John 8               10000
9      John 9               20000
10     John 10              50000

Then in cell Q10 I have value, let's say 32000

I would like to display column A range dynamically in ActiveX ComboBox11 with condition:

=IF(B1<Q10;"not in list";A1))

so that I would not have values less than 32000 in ActiveX ComboBox11 drop down.

In this case ActiveX ComboBox11 would look like:

John 5
John 6
John 7
John 10

It is easy to have named range in ListFillRange. However I would like to have a dynamic range. Any ideas how to achieve that?

I need to use filter somehow?

Private Sub Worksheet_Change(ByVal Target As Range)

   'If Target.Address = Range("Q10").Address Then
       'Range("A1:B10").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("B1:B10")
   'End If

End Sub

And after that use filtered range in ActiveX ComboBox11 ListFillRange?

Ricardo A Ricardo A · Accepted Answer · 2019-04-17T15:52:28

There are a few ways to do this. You can loop all rows, find the values higher than 3200, copy them to a Hidden sheet, when done with the loop, get the Range from this Hidden Sheet and Assign it to the ListFillRange. Example:

Function updateCombo1()
    Dim lastRow As Long, i As Long, x As Long, wk As Workbook
    Set wk = ThisWorkbook
    With wk.Sheets("Sheet1")
        lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        wk.Sheets("HiddenSheet").Cells.ClearContents
        For i = 1 To lastRow
            If .Cells(i, 3).Value >= .Range("G10").Value Then
                x = x + 1
                wk.Sheets("HiddenSheet").Cells(x, 1).Value = .Cells(i, 2).Value
            End If
        Next i
        lastRow = wk.Sheets("HiddenSheet").Cells(Rows.Count, 1).End(xlUp).Row
        'If Form DropDown, Set Range
        .DropDowns("Drop Down 2").ListFillRange = "HiddenSheet!A1:A" & lastRow
        'If ActiveX DropDown, Set Range
        .ComboBox11.ListFillRange = "HiddenSheet!A1:A" & lastRow
    End With
End Function

You can also loop all rows and add one by one on the drop down. Example:

Function updateCombo2()
    Dim lastRow As Long, i As Long, wk As Workbook
    Set wk = ThisWorkbook
    With wk.Sheets("Sheet1")
        lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        'If Form Drop Down, Clear All Items
        .DropDowns("Drop Down 2").RemoveAllItems
        'If ActiveX Drop Down, Clear All Items
        .ComboBox11.Clear
        For i = 1 To lastRow
            If .Cells(i, 3).Value >= .Range("Q10").Value Then
                'If Form DropDown, Add Item
                .DropDowns("Drop Down 2").AddItem .Cells(i, 2).Value
                'If ActiveX DropDown, Add Item
                .ComboBox11.AddItem .Cells(i, 2).Value
            End If
        Next i
    End With
End Function

Just call the Function on Worksheet_Change

Edit: It is much simpler to work with an ActiveX Drop Down, but for compatibility I would try to use the Form Drop Down instead.

ActiveX ComboBox dynamic range for ListFillRange

1 Answers