ActiveX ComboBox dynamic range for ListFillRange

[mrwad]

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 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?

Also asked here: https://stackoverflow.com/questions/55730455

 

[mrwad]

I have used empty cells ""

and this code

Private Sub Worksheet_Change(ByVal Target As Range)


   'If Target.Address = Range("E216").Address Then
       'Range("A219:E235").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("E219:E235")
   'End If
Dim c As Range
ThisWorkbook.Sheets("MAIN").ComboBox11.Clear
With ThisWorkbook.Sheets("Contact database")
    For Each c In .Range(.Range("A219"), .Range("A" & .Rows.Count).End(xlUp))
        If c.Value <> vbNullString Then ThisWorkbook.Sheets("MAIN").ComboBox11.AddItem c.Value
    Next c
End With
End Sub