dcsimg
Results 1 to 3 of 3

Thread: [RESOLVED] ActiveX ComboBox dynamic range for ListFillRange

  1. #1

    Thread Starter
    Member
    Join Date
    Oct 2018
    Posts
    38

    Resolved [RESOLVED] ActiveX ComboBox dynamic range for ListFillRange

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

    Code:
                 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:

    Code:
    =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:


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

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

  2. #2

    Thread Starter
    Member
    Join Date
    Oct 2018
    Posts
    38

    Re: ActiveX ComboBox dynamic range for ListFillRange

    SOLVED! I have used empty cells ""

    and this code

    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

  3. #3
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,247

    Re: ActiveX ComboBox dynamic range for ListFillRange

    pls mark thread resolved
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width