search using textbox and display on list box in userform

blxck · Dec 1st 2016

I have a user form with a listbox, textbox, and command button. The intent is for a user to type into the textbox, click the button, and the code would search through the database for the text. database consist of 5 column like below;
1. client
2. province
3.district
4.commune
5. village

im trying to search for the client name example "sorn" or "sorn tye" than it will display all the related information regarding that name in a listbox. for now, the listbox will display all the info in the database not the one that i search in the textbox.

Below is some code I've been trying to change to work. Not sure if this is a step in the right direction.

Code

Private Sub cmdAnalysis_Click()
'dim the variables
Dim FindMe As Range
Dim DataSH As Worksheet
'error handler
On Error GoTo errHandler:
'set object variables
Set DataSH = DataBase
'hold in memory and stop screen flicker
Application.ScreenUpdating = False
'//////////////////////////////////////////
'find the value in the column
Columns("D:H").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "AG5:AG6"), CopyToRange:=Range("AI5:AM5"), Unique:=False
        
Set searchme = Range("AG6")
    Set FindMe = Range("D2:H1572").Find(What:=searchme, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
        FindMe.Select
        


'/////////////////////////////////////////
'unprotect all sheets
'Unprotect_All
'filter the data
Columns("D:H").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("AG5:AG6"), CopyToRange:=Range("AI5:AM5"), _
Unique:=False
'add the dynamic data to the listbox
lstAnalysis.RowSource = DataSH.Range("DataList").Address(external:=True)
'protect all sheets
'Protect_All
'error handler
On Error GoTo 0
Exit Sub
errHandler:
'Protect all sheets
'Protect_All
'if error occurs then show me exactly where the error occurs
MsgBox "No match found for " & txtSearch.Text
'clear the listbox if no match is found
Me.lstAnalysis.RowSource = ""
Exit Sub
End Sub

Display More

**KjBox** · Dec 1st 2016

Re: search using textbox and display on list box in userform

It would help greatly if you could attach a workbook, complete with your user form.
[sw]*[/sw]

**royUK** · Dec 1st 2016

Re: search using textbox and display on list box in userform

Take a look at my DataBaseForm, free to download here

blxck · Dec 1st 2016

Re: search using textbox and display on list box in userform

since i cant show you the database that im working on, this is the example exactly like what i have

**royUK** · Dec 2nd 2016

Re: search using textbox and display on list box in userform

Have you checked out my example?

**KjBox** · Dec 3rd 2016

Re: search using textbox and display on list box in userform

Is this what you are trying to do?

I changed the dynamic named range ("DataList") you were using. It will now cover just the data in your database and exclude the headers plus many empty rows below the table which the previous named range included.

There is no need to have a command button on UserForm2, this is the code will load the ListBox with filtered data from your database for any change to the text in the TextBox.

Code

Private Sub TextBox1_Change()
    Dim x, i As Long, ii As Long, iii As Integer
    x = [DataList]
    
    With ListBox1
        If TextBox1 = "" Then
            .RowSource = "DataList"
        Else
            .RowSource = ""
            For i = 1 To UBound(x, 1)
                If LCase(x(i, 5)) Like LCase(TextBox1) & "*" Then
                    For ii = 1 To 5
                        .AddItem
                        .List(iii, ii - 1) = x(i, ii)
                    Next
                    iii = iii + 1
                End If
            Next
        End If
    End With
    
End Sub

Display More

I have used Labels for the ListBox column headers (placed immediately above the ListBox) instead of using the ListBox ColumnHeader Property. This is much easier when the ListBox needs to be dynamic.

**royUK** · Dec 3rd 2016

Re: search using textbox and display on list box in userform

Much simpler to use AutoFilter, it is also not clear which column is to be searched. This is taken from my form and works in the example workbook with some changes to the column references.

Code

Sub FindAll()
    Dim wesTemp As Worksheet
    Dim strFind As String    'what to find


    strFind = Me.TextBox1.Value


    If Not Ws.AutoFilterMode Then MyData.AutoFilter


    MyData.AutoFilter Field:=1, Criteria1:=strFind


    Me.ListBox1.Clear
    For Each c In MyData.Columns(1).SpecialCells(xlCellTypeVisible)
        With ListBox1
            .AddItem c.Value
            .List(.ListCount - 1, 1) = c.Offset(0, 1).Value
            .List(.ListCount - 1, 2) = c.Offset(0, 2).Value
            .List(.ListCount - 1, 3) = c.Offset(0, 3).Value
            .List(.ListCount - 1, 4) = c.Offset(0, 4).Value
            .List(.ListCount - 1, 5) = c.Offset(0, 5).Value
        End With
    Next c


End Sub

Display More

In more complex versions I would automate AdvancedFilter

blxck · Dec 5th 2016

Re: search using textbox and display on list box in userform

yeah i worked perfectly like what i want. but can you explain to me why did you changed the dynamic range "DataList" again. im still new here and im still learning. thank you. anyway sorry for the late reply

blxck · Dec 5th 2016

Re: search using textbox and display on list box in userform

and 1 more thing, database that i worked on now consist of hundreds of data. will it display all the data once we open the userform.?

**KjBox** · Dec 5th 2016

Re: search using textbox and display on list box in userform

I changed the "DataList" dynamic named range because the formula you had used included the header row and also extended down many empty rows below the database. The change I made means the header row is not included and only rows containing data are referenced.

Yes the full database will be loaded into the List Box when the User Form is opened.

blxck · Dec 5th 2016

Re: search using textbox and display on list box in userform

i have tried that sample code on my database but nothing come out on the userform. i cant search the name and full database is not loaded in the listbox.

**KjBox** · Dec 5th 2016

Re: search using textbox and display on list box in userform

Does your database in you actual file still start in column B with Row 3 being the header row and the data starting in Cell B4?

Did you copy the formula for the dynamic named range ("DataList") correctly, is that named range still named "DataList"?

blxck · Dec 6th 2016

Re: search using textbox and display on list box in userform

no, it start with column D with row 1 being the header row and data starting in cell D2.

and yes, i have changed my dynamic named range to ("myData")

Code

Private Sub txtSearch_Change()
Dim x, i As Long, ii As Long, iii As Integer
    x = [myData]
    
    With lstAnalysis
        If txtSearch = "" Then
            .RowSource = "myData"
        Else
            .RowSource = ""
            For i = 1 To UBound(x, 1)
                If LCase(x(i, 5)) Like LCase(txtSearch) & "*" Then
                    For ii = 1 To 5
                        .AddItem
                        .List(iii, ii - 1) = x(i, ii)
                    Next
                    iii = iii + 1
                End If
            Next
        End If
    End With
    
End Sub

Display More

**KjBox** · Dec 7th 2016

Re: search using textbox and display on list box in userform

The changes you made to the code were correct.

I suspect that you got the formula wrong for the dynamic Named Range "myData". With your data table starting in Cell D1 and Row 1 being the header row the formula should be

=OFFSET(Sheet1!$D$2,,,COUNTA(Sheet1!$D:$D)-1,5)

See the attached file for working example.

blxck · Dec 8th 2016

Re: search using textbox and display on list box in userform

thank you for your help. i've got the solution. you are right its because of my dynamic names range.. btw thank you for your help

blxck · Dec 8th 2016

Re: search using textbox and display on list box in userform

ahh another one, i have to do update button for my form. how can i choose data from list box and update it using textbox and combobox?

rich37 · Apr 6th 2017

Re: search using textbox and display on list box in userform

Quote from blxck;781883

no, it start with column D with row 1 being the header row and data starting in cell D2.

and yes, i have changed my dynamic named range to ("myData")

Code

Private Sub txtSearch_Change()
Dim x, i As Long, ii As Long, iii As Integer
    x = [myData]
    
    With lstAnalysis
        If txtSearch = "" Then
            .RowSource = "myData"
        Else
            .RowSource = ""
            For i = 1 To UBound(x, 1)
                If LCase(x(i, 5)) Like LCase(txtSearch) & "*" Then
                    For ii = 1 To 5
                        .AddItem
                        .List(iii, ii - 1) = x(i, ii)
                    Next
                    iii = iii + 1
                End If
            Next
        End If
    End With
    
End Sub

Display More

Great it's work for me :thumbcoo: how to edit this code when i choose optionbutton 1 to select this code or optionbutton 2 to select another code

**KjBox** · Apr 6th 2017

Re: search using textbox and display on list box in userform

Do you mean option buttons on a user form or on a worksheet?

rich37 · Apr 6th 2017

Re: search using textbox and display on list box in userform

Quote from KjBox;789910

Do you mean option buttons on a user form or on a worksheet?

KjBoxthank you for reply me answer 's on userform.

[ATTACH=CONFIG]72090[/ATTACH]

search using textbox and display on list box in userform

Participate now!

Share

Users Viewing This Thread