Search & fill listbox with textbox error after 10th column ?

[Tayl4n]

Dear MrExcel community,

I have project with 32 column. I want to realtime fill listbox with textbox ("txtK" "K" column) and it must show 32 columns data.
I heard about 10 column limit with additem but I don't know how can I do that same function with other methods. I don't know other methods or how can I solve this.
Code function is working clearly until 10th column.

Private_sub txtK_Change()

Dim sh As Worksheet
Set sh = Sheets("Worksheet")
Dim i As Long
Dim x As Long
Dim p As Long
Me.ListBox1.Clear
Me.ListBox1.ColumnCount = 32
Me.ListBox1.ColumnHeads = False
Me.ListBox1.ColumnWidths = ""

Me.ListBox1.AddItem "Plaka"

Me.ListBox1.List(ListBox.ListCount - 1, 1) = "Header 1"
Me.ListBox1.List(ListBox.ListCount - 1, 2) = "Header 2"
Me.ListBox1.List(ListBox.ListCount - 1, 3) = "Header 3"
Me.ListBox1.List(ListBox.ListCount - 1, 4) = "Header 4"
Me.ListBox1.List(ListBox.ListCount - 1, 5) = "Header 5"
Me.ListBox1.List(ListBox.ListCount - 1, 6) = "Header 6"
Me.ListBox1.List(ListBox.ListCount - 1, 7) = "Header 7"
Me.ListBox1.List(ListBox.ListCount - 1, 8) = "Header 8"
Me.ListBox1.List(ListBox.ListCount - 1, 9) = "Header 9"

For i = 2 To sh.Range("K" & Rows.Count).End(xlUp).Row
For x = 1 To Len(sh.Cells(i, 11))
p = Me.txtK.TextLength

If Mid(sh.Cells(i, 11), x, p) = Me.txtK And txtK <> "" Then
With Me.ListBox1

.AddItem sh.Cells(i, 11)

.List(ListBox1.ListCount - 1, 1) = sh.Cells(i, 1)
.List(ListBox1.ListCount - 1, 2) = sh.Cells(i, 2)
.List(ListBox1.ListCount - 1, 3) = sh.Cells(i, 3)
.List(ListBox1.ListCount - 1, 4) = sh.Cells(i, 4)
.List(ListBox1.ListCount - 1, 5) = sh.Cells(i, 5)
.List(ListBox1.ListCount - 1, 6) = sh.Cells(i, 6)
.List(ListBox1.ListCount - 1, 7) = sh.Cells(i, 7)
.List(ListBox1.ListCount - 1, 8) = sh.Cells(i, 8)
.List(ListBox1.ListCount - 1, 9) = sh.Cells(i, 9)

.List(ListBox1.ListCount - 1, 10) = sh.Cells(i, 10) '''''Error Line''''


End With
End If

Next x
Next i
End Sub

Thank you for your time and interest.

 

[Kenneth Hobson]

You can set up a scratch worksheet and then add remove data to that. Then use LIST to fill/refresh the listbox. e.g.

Private Sub UserForm_Initialize()
  ListBox1.ColumnCount = 30
  ListBox1.List = Range("A1:AD7").Value
End Sub

Private Sub CommandButton1_Click()
  ListBox1.List = Range("A2:AD10").Value
End Sub

 

[Tayl4n]

You can set up a scratch worksheet and then add remove data to that. Then use LIST to fill/refresh the listbox. e.g.

Private Sub UserForm_Initialize()
  ListBox1.ColumnCount = 30
  ListBox1.List = Range("A1:AD7").Value
End Sub

Private Sub CommandButton1_Click()
  ListBox1.List = Range("A2:AD10").Value
End Sub

It’s work! Thank you so much!