Populating a Combobox based on condition

[gemcgraw]

I can't use the RowSource option to populate a comboBox. I need to only populate it with values based on another column's value.

Scenario:
ComboBoxRoom should only be populated from values in Worksheets("PrinterDatabase") C2:C200, if the corresponding column on that sheet, H2:H200 value is not blank -or- null. If so, skip until the end is reached. It's that simple but for some reason I'm just having a mental block.

 

[dmt32]

Hi,
Couple of suggestions to populate your combobox from range values

1 – AddItem Method

     Dim Rooms As Range, Cell As Range

    Set Rooms = ThisWorkbook.Worksheets("PrinterDatabase").Range("C2:C200")
    
    For Each Cell In Rooms
        If Len(Cell.Offset(0, 5).Value) > 0 Then Me.ComboBoxRoom.AddItem Cell.Value
    Next Cell

If though, more than 10 items are being added, this approach can reduce the speed of your code

2 – List Property

    Dim Rooms As Range, Cell As Range    
    Dim arr() As Variant
    Dim i As Integer
    
    
    Set Rooms = ThisWorkbook.Worksheets("PrinterDatabase").Range("C2:C200")
    
    For Each Cell In Rooms
        If Len(Cell.Offset(0, 5)) > 0 Then
        i = i + 1
        ReDim Preserve arr(1 To i)
        arr(i) = Cell.Value
        End If
    Next Cell
    If i > 0 Then Me.ComboBoxRoom.List = arr

You can directly assign any 1-dimensional or 2-dimensional array to a combobox.
If you are adding more than 1 item to your combobox I personally, would store values in an array first. You then assign the array in one go using List Property. This approach is generally considered the most efficient way to populate such a control.

Hope Helpful

Dave

 

[gemcgraw]

Dmt32 - Thanks for the two suggestions! However, when I tested both to see which would be more efficient, I get a 'Runtime error 70 - Permission Denied' I don't know that I've ever seen one of those in my days of writing code. Indicates there is a write protect somewhere. I've restarted the machine, thinking Excel was hung up. But the only way to resolve it is to comment-out that snippet of new code. Any thoughts?

Hi,
Couple of suggestions to populate your combobox from range values

1 – AddItem Method

     Dim Rooms As Range, Cell As Range

    Set Rooms = ThisWorkbook.Worksheets("PrinterDatabase").Range("C2:C200")
    
    For Each Cell In Rooms
        If Len(Cell.Offset(0, 5).Value) > 0 Then Me.ComboBoxRoom.AddItem Cell.Value
    Next Cell

If though, more than 10 items are being added, this approach can reduce the speed of your code

2 – List Property

    Dim Rooms As Range, Cell As Range    
    Dim arr() As Variant
    Dim i As Integer
    
    
    Set Rooms = ThisWorkbook.Worksheets("PrinterDatabase").Range("C2:C200")
    
    For Each Cell In Rooms
        If Len(Cell.Offset(0, 5)) > 0 Then
        i = i + 1
        ReDim Preserve arr(1 To i)
        arr(i) = Cell.Value
        End If
    Next Cell
    If i > 0 Then Me.ComboBoxRoom.List = arr

You can directly assign any 1-dimensional or 2-dimensional array to a combobox.
If you are adding more than 1 item to your combobox I personally, would store values in an array first. You then assign the array in one go using List Property. This approach is generally considered the most efficient way to populate such a control.

Hope Helpful

Dave

 

[Norie]

Have you checked that the RowSource property of the combobox is clear?

 

[gemcgraw]

Oh man! I feel so little right now. I should have looked at that first. Thank you Norie!!