CODEWITHSUNDEEP
excelvbaexcel-formula
BCLtd
BCLtd

Reputation: 1461

Populate Listbox With Criteria

I have populated a listbox from a range, which shows like this

enter image description here

Using the following code:

    Private Sub UserForm_Initialize()
    Dim rngOneColumn As Range
    

    'Define the source range
    Set rngOneColumn = ThisWorkbook.Worksheets("config").Range("E2:E21")
    
    'Populate listbox item
    ListBox1.List = rngOneColumn.Cells.Value
    
    
End Sub

This populates it from the following sheet.

enter image description here

However, what I want to display is two columns: Room & Minor

and then only list room numbers in the list box where Room In Use column (J) is equal to false, and in the second column display if Room minor is Yes or No from column (F)

Upvotes: 0

Views: 1146

Answers (1)

tigeravatar
tigeravatar

Reputation: 26640

Given your setup, something like this should work for you:

Private Sub UserForm_Initialize()

    Dim wsConf As Worksheet
    Dim aData As Variant
    Dim aOpenRooms As Variant
    Dim ixData As Long
    Dim ixOpen As Long
    Dim lRoomUseCol As Long
    Dim lRoomInterpsCol As Long
    Dim lRoomMinorCol As Long

    'Adjust these as needed for your data
    Set wsConf = ThisWorkbook.Worksheets("config")
    lRoomUseCol = 10    'column J
    lRoomInterpsCol = 5 'column E
    lRoomMinorCol = 6   'column F

    With wsConf.Range("A1").CurrentRegion
        ReDim aOpenRooms(1 To WorksheetFunction.CountIf(Intersect(.Parent.Columns(lRoomUseCol), .Cells), False), 1 To 2)
        aData = .Value
    End With

    For ixData = LBound(aData, 1) To UBound(aData, 1)
        If aData(ixData, lRoomUseCol) = False Then
            ixOpen = ixOpen + 1
            aOpenRooms(ixOpen, 1) = aData(ixData, lRoomInterpsCol)
            aOpenRooms(ixOpen, 2) = aData(ixData, lRoomMinorCol)
        End If
    Next ixData

    With Me.ListBox1
        .Clear
        .ColumnCount = UBound(aOpenRooms, 2)
        .List = aOpenRooms
    End With

End Sub

Upvotes: 1

Related Questions