• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Problem with list box with 12 columns and search textbox

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?):
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
 

jonastiger

Your ... what is wrong?
What happens when You're running that?
One wondering:
... from col B to col M.
and
= sh.Cells(i, 1) take something from (1) A-column ... hmm?
 
First, the userform only shows if I deactivate the two last columns:
...
'Me.ListBox1.List(0, 10) = "GROUP"
'Me.ListBox1.List(0, 11) = "OBS"
...
'Me.ListBox1.List(ListBox1.ListCount - 1, 10) = sh.Cells(i, 11)
'Me.ListBox1.List(ListBox1.ListCount - 1, 11) = sh.Cells(i, 12)

It seems is missing some code to aloow more than 10 columns.
I've already tried:
Code:
Me.ListBox1.ColumnCount = 12
but nothing happens

Then, no matter if it is sh.Cells(i, 1) or sh.Cells(i, 2), the result is always the one below:
1714510760117.png
 
Hi ! This is very not the right way as written within VBA help so you have to first see the help of ListBox.ColumnCount property, a must read …​
In a second time, as Excel features have all the necessary to search whatever the data so what could be the benefit of such UserForm,​
why reinventing the wheel with the risk of a squared one ?!​
 
Hi
For what I want, only Excel "as is" it's not enough. This is a part of a small client management with automated routines I'm trying to built, which has other userforms, tables and files.
In fact, there are many similar examples in the net but, so far, none of those I had explored suit my needs, so I decided to create one.

This is the first time I work with Listbox. In this case, I can populate the listbox through
Code:
Private Sub CommandButton1_Click() 'UPDATE DATA
    ListBox1.ColumnCount = 12
    ListBox1.RowSource = "BD_CLIENTS"
End Sub

My issue at the moment is in the search textbox ("Pesquisa")
 
Last edited by a moderator:
You still haven't actually said what your issue is though.
with the code in first post, for search in textbox, I have the result inthe third post. I know it's missing something...
my goal is that, while typing in textbox, it would show filtered results in listbox
 
Last edited:
You can only use 10 columns with AddItem. You need to use an array or a range on a sheet. Since you've already populated the listbox, you can read its data into an array from its List property, then loop through that and populate another array. Alternatively, you could use a hidden sheet, and simply filter the data from the main sheet to the hidden sheet based on the contents of the textbox and then update the rowsource.

If you want to use actual column headers, you have to use a range on a worksheet.
 
10 rows only with AddItems as explained in ColumnCount help …​
For the useless (according to the initial post code) UserForm a specific array variable can be used in order to be managed​
with the VBA Filter function among other ways but as it depends on what we can't see …​
 
Back
Top