Help with list boxes please.

[evertjvr]

I need help with a list box.

The list box must contain data that is spread over 25 non-sequential columns
The rows must also be non-sequential (can grow to many) and only contain the search criteria set in a text box and a combobox combined.

textbox2 in the attached file is only there to show where the file is now but I know how the do that one.

List Box History.xlsm

Is there a better way to do it?

Thanks

[wallyeye]

You can add the items individually:

Private Sub ComboBox1_Change()

    Call PopulateList

End Sub

Private Sub TextBox1_Change()

    Call PopulateList

End Sub

Public Sub PopulateList()
'
'  Adds items to the list from the worksheet
'
    Dim arrList         As Variant

    Dim lngLastRow      As Long
    Dim lngItem         As Long
    Dim lngItems        As Long

    arrList = rngList
    ListBox1.Clear

    lngItems = 0
    lngLastRow = Sheet1.Columns(6).Find(What:="*", After:=[F1], _
        SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
        LookAt:=xlPart, LookIn:=xlValues).Row
    arrList = Sheet1.Cells(3, 1).Resize(lngLastRow - 2, 27)
    For lngItem = LBound(arrList) To UBound(arrList)
        If CStr(arrList(lngItem, 6)) = Me.TextBox1.Value _
        And arrList(lngItem, 12) = Me.ComboBox1 Then
            Me.ListBox1.AddItem arrList(lngItem, 1)
            Me.ListBox1.List(lngItems, 1) = arrList(lngItem, 2)
            lngItems = lngItems + 1
        End If
    Next lngItem

End Sub

I added a second column to the list, you can add as many as you need (wrong, only nine). The me.listbox1.list(lngitems,1) line can be duplicated and incremented for the remaining items.

I would recommend renaming your controls to something more meaningful, ComboBox1 is ok while you are working on it but in six months you'll be struggling with it. cboFileType would be a better name.

[evertjvr]

Thanks for your help but I seem to have a problem with this code - it does not work.
I do believe the problem lies with me though.

Thanks for the advice on the renaming id the objects. I have actually started on that just today.

[evertjvr]

I found the problem. Some how some spaces in my Initalize routine got lost so the code could never find the value. (I told you that the I was the problem)

Thanks for your help.

[evertjvr]

Ok. I now know that I need to learn how to use arrays fast.

The code works (now just better) but it crashes if the array source column goes 10 or bigger please help

List Box History.xlsm

Thanks

[jindon]

AddItem method only allow up to 9 columns as far as I remember.

You will need to create an array for the list/column property of ListBox.

e.g

Public Sub PopulateList()
'
'  Adds items to the list from the worksheet
'
    Dim arrList         As Variant

    Dim lngLastRow      As Long
    Dim lngItem         As Integer
    Dim lngItems        As Integer
    Dim myList()        As String

    ListBox1.Clear
    
    ListBox1.ColumnCount = 20
    
    lngItems = 0
    lngLastRow = Sheet1.Columns(6).Find(What:="*", After:=[F1], _
        SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
        LookAt:=xlPart, LookIn:=xlValues).Row
    arrList = Sheet1.Cells(3, 1).Resize(lngLastRow - 2, 27)
    ReDim myList(1 To 20, 1 To lngLastRow)
    For lngItem = LBound(arrList) To UBound(arrList)
        If CStr(arrList(lngItem, 6)) = Me.TextBox1.Value _
            And arrList(lngItem, 12) = Me.ComboBox1 Then
            lngItems = lngItems + 1
            For i = 2 To 20
                myList(i - 1, lngItems) = arrList(lngItem, i)
            Next
        End If
    Next
    If lngItems > 0 Then
        ReDim Preserve myList(1 To 20, 1 To lngItems)
        Me.ListBox1.Column = myList
    End If
ActiveWorkbook.Save
End Sub

[evertjvr]

I managed to stretch the list to include all the entries but now I get all the columns ad I only want some.

I will try some more but please help.

[wallyeye]

You can just reduce the size of the list:

Redim mylist(1 to xx, 1 to lngLastRow)

Where xx is the total number of columns you want.

Then, inside the lngItem loop, just assign the specific columns:

lngitems = lngitems + 1
  mylist(1,lngitems) = arrlist(lngitem,2)
  mylist(2,lngitems) = arrlist(lngitem,3)

and so on, picking and choosing the columns you want. At the bottom, change the last redim to match your columns again:

Redim preserve mylist(1 to xx, 1 to lngitems)

BTW, thank you jindon for pointing out the limitation on .additem and showing a good method for extending the list.

[evertjvr]

Thanks to Wallyeye and Jindon.

The Code works perfect and I'm happy. (at least for now)