jonastiger
Member
Hi
I'm trying to creat a userform with a 12 collumn listbox and with a a search textbox:
Database is a table in BD_CLIENTS sheet from col B to col M.
I would be very thankful if someone help me with this code (what is wrong?):
Thanks in advance
I'm trying to creat a userform with a 12 collumn listbox and with a a search textbox:
Database is a table in BD_CLIENTS sheet from col B to col M.
I would be very thankful if someone help me with this code (what is wrong?):
Code:
Private Sub TextBox11_Change() 'Pesquisa
Dim i As Long
Dim x As Long
Dim a As Long
Me.TextBox11 = Format(StrConv(Me.TextBox11.Text, vbLowerCase))
Me.ListBox1.AddItem "ID_CLIENT"'numeric'
Me.ListBox1.List(0, 1) = "NAME"
Me.ListBox1.List(0, 2) = "ADDRESS"
Me.ListBox1.List(0, 3) = "NIF"
Me.ListBox1.List(0, 4) = "CONTACT 1"
Me.ListBox1.List(0, 5) = "CONTACT 2"
Me.ListBox1.List(0, 6) = "EMAIL"
Me.ListBox1.List(0, 7) = "RUTE"
Me.ListBox1.List(0, 8) = "REGIST"'date/timestamp'
Me.ListBox1.List(0, 9) = "STATUS"
Me.ListBox1.List(0, 10) = "GROUP"
Me.ListBox1.List(0, 11) = "OBS"
Me.ListBox1.Selected(0) = True
For i = 2 To Sheet1.Range("B" & Rows.Count).End(xlUp).Row
For x = 1 To Len(Sheet1.Cells(i, 2))
a = Me.TextBox11.TextLength
If LCase(Mid(Sheet1.Cells(i, 2), x, a)) = Me.TextBox11 And Me.TextBox11 <> "" Then
Me.ListBox1.AddItem Sheet1.Cells(1, 2)
Me.ListBox1.List(ListBox1.ListCount - 1, 0) = sh.Cells(i, 1)
Me.ListBox1.List(ListBox1.ListCount - 1, 1) = sh.Cells(i, 2)
Me.ListBox1.List(ListBox1.ListCount - 1, 2) = sh.Cells(i, 3)
Me.ListBox1.List(ListBox1.ListCount - 1, 3) = sh.Cells(i, 4)
Me.ListBox1.List(ListBox1.ListCount - 1, 4) = sh.Cells(i, 5)
Me.ListBox1.List(ListBox1.ListCount - 1, 5) = sh.Cells(i, 6)
Me.ListBox1.List(ListBox1.ListCount - 1, 6) = sh.Cells(i, 7)
Me.ListBox1.List(ListBox1.ListCount - 1, 7) = sh.Cells(i, 8)
Me.ListBox1.List(ListBox1.ListCount - 1, 8) = sh.Cells(i, 9)
Me.ListBox1.List(ListBox1.ListCount - 1, 9) = sh.Cells(i, 10)
Me.ListBox1.List(ListBox1.ListCount - 1, 10) = sh.Cells(i, 11)
Me.ListBox1.List(ListBox1.ListCount - 1, 11) = sh.Cells(i, 12)
End If
Next x
Next i
End Sub
Thanks in advance