ListBox.List(ListBox.ListCount -1, 10) PROBLEM

**Roque_Trinidad** · 02-07-2024, 12:57 PM

Hi guys!

I'd appreciate your help with the following concern.

I'm trying to complete a ListBox content; however I'm struggling with a simple detail that I don't know how to deal with.

I noticed that the second part of the list content doesn't accept two digits. Here is the code below. (The problem runs from the "ListCount - 1, 10" and so on)

last_row = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).row

For row_l = 3 To last_row

If UCase(Cells(row_l, 4).Value) Like "*" & UCase(Me.TBsearch_bid.Value) & "*" Or UCase(Cells(row_l, 3).Value) Like "*" & UCase(Me.TBsearch_bid.Value) & "*" Or UCase(Cells(row_l, 5).Value) Like "*" & UCase(Me.TBsearch_bid.Value) & "*" Then

Me.ListBox1.AddItem Cells(row_l, 1)

Me.ListBox1.List(ListBox1.ListCount - 1, 0) = Cells(row_l, 1)
Me.ListBox1.List(ListBox1.ListCount - 1, 1) = Cells(row_l, 2)
Me.ListBox1.List(ListBox1.ListCount - 1, 2) = Cells(row_l, 3)
Me.ListBox1.List(ListBox1.ListCount - 1, 3) = Cells(row_l, 4)
Me.ListBox1.List(ListBox1.ListCount - 1, 4) = Cells(row_l, 5)
Me.ListBox1.List(ListBox1.ListCount - 1, 5) = Cells(row_l, 6)
Me.ListBox1.List(ListBox1.ListCount - 1, 6) = Cells(row_l, 7)
Me.ListBox1.List(ListBox1.ListCount - 1, 7) = Cells(row_l, 8)
Me.ListBox1.List(ListBox1.ListCount - 1, 8) = Cells(row_l, 9)
Me.ListBox1.List(ListBox1.ListCount - 1, 9) = Cells(row_l, 10)
Me.ListBox1.List(ListBox1.ListCount - 1, 10) = Cells(row_l, 11) <-
Me.ListBox1.List(ListBox1.ListCount - 1, 11) = Cells(row_l, 12)
Me.ListBox1.List(ListBox1.ListCount - 1, 12) = Cells(row_l, 13)
Me.ListBox1.List(ListBox1.ListCount - 1, 13) = Cells(row_l, 14)
Me.ListBox1.List(ListBox1.ListCount - 1, 14) = Cells(row_l, 15)
Me.ListBox1.List(ListBox1.ListCount - 1, 15) = Cells(row_l, 16)
Me.ListBox1.List(ListBox1.ListCount - 1, 16) = Cells(row_l, 17)

End If

Next row_l

Thanks in advance guys!

**romperstomper** · 02-07-2024, 01:01 PM

You can only use 10 columns if you use AddItem to populate the listbox. You need to either use a range and the RowSource property or use an array assigned to the List/Column property if you need more columns.

**romperstomper** · 02-07-2024, 01:08 PM

Untested but something like this for example:

   last_row = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
   Dim matchList()
   Dim maxMatches As Long
   maxMatches = Application.CountIf(Sheets("Sheet2").Range("C3:E" & last_row), "*" & Me.TBsearch_bid.Value & "*")
   ' Note: array is transposed as we need to resize it
   ReDim matchList(1 To 17, 1 To maxMatches)
   Dim counter As Long
   For row_l = 3 To last_row
   
      If UCase(Cells(row_l, 4).Value) Like "*" & UCase(Me.TBsearch_bid.Value) & "*" _
            Or UCase(Cells(row_l, 3).Value) Like "*" & UCase(Me.TBsearch_bid.Value) & "*" _
            Or UCase(Cells(row_l, 5).Value) Like "*" & UCase(Me.TBsearch_bid.Value) & "*" Then
         counter = counter + 1
         Me.ListBox1.AddItem Cells(row_l, 1)
         Dim colNum As Long
         For colNum = 1 To 17
            matchList(colNum, counter) = Cells(row_l, colNum)
         Next colNum
      
      End If
   
   Next row_l
   ReDim Preserve matchList(1 To 17, 1 To counter)
   ' using Column rather than List will transpose the array back the way we want it
   Me.ListBox1.Column = matchList

**Roque_Trinidad** · 02-07-2024, 01:21 PM

Thank you!

**Roque_Trinidad** · 02-07-2024, 01:18 PM

Thanks! I'll try with RowSource or the other options.

ListBox.List(ListBox.ListCount -1, 10) PROBLEM

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

ListBox.List(ListBox.ListCount -1, 10) PROBLEM

Re: ListBox.List(ListBox.ListCount -1, 10) PROBLEM

Re: ListBox.List(ListBox.ListCount -1, 10) PROBLEM

Re: ListBox.List(ListBox.ListCount -1, 10) PROBLEM

Re: ListBox.List(ListBox.ListCount -1, 10) PROBLEM

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] listbox to listbox data transfering problem

Listbox.listcount property - application defined or object defined error

Listbox LISTCOUNT propery problem

[SOLVED] How do I populate a listbox with a list excluding values found in another listbox?

Incorrect ListCount value in ListBox

Listbox to Listbox, no duplicates & submitting same UserForm data for each Listbox entry.

dynamic listbox -- manually change .ListCount property

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions