[RESOLVED] Search multiple columns in userform and edit results

[hrzagi]

I'm trying to work out the code needed to filter or search a listbox included on a userform that contains multiple columns and multiple rows. So far i figured out how to search just one column. Also, if you could help me to figure out how can I select result from listbox and that automaticly fill textboxes so i could edit entry (so far i could only edit if i search by number and then when i enter existing number result is populated in textboxes so i could edit them). I attached project so you can see my code and what i did so far. Thank you and sorry for bad english.
https://www.dropbox.com/s/ud60saw5ifw4ejr/Program v.2.xlsm?dl=0

[Zvoni]

Are you using the RowSource or List-Property of the Listbox to populate your Listbox?

[westconn1]

do you want to search the listbox as requested? or the underlying worksheet?
if you want to search the worksheet, you can use excels' find method, to just return the row, then populate the textboxes from that row

try like

Private Sub cmbSearch_Click()
Dim iSearch As Long, i As Long
Dim fnd As Range
Set fnd = Worksheets("Sheet1").Range("A1").CurrentRegion.Find(txtSearch)
If fnd Is Nothing Then MsgBox "not found": Exit Sub
i = fnd.Row
''For i = 1 To iSearch
''
''If Trim(Sheet1.Cells(i, 1)) <> Trim(txtSearch.Text) And i = iSearch Then
''MsgBox ("Nema podataka")
''txtSearch.Text = ""
''txtSearch.SetFocus
''End If

''If Trim(Sheet1.Cells(i, 1)) = Trim(txtSearch.Text) Then
txtRedniBroj.Text = Sheet1.Cells(i, 1)
txtNazivPismena.Text = Sheet1.Cells(i, 2)
txtBrojPismena.Text = Sheet1.Cells(i, 3)
txtPosaljitelj.Text = Sheet1.Cells(i, 4)
txtDatumZaprimanja.Text = Sheet1.Cells(i, 5)
txtDatumRazduzenja.Text = Sheet1.Cells(i, 6)
cmbPolicajac.Text = Sheet1.Cells(i, 7)
cmbStatus.Text = Sheet1.Cells(i, 8)
''Exit For
''End If

''Next i
End Sub

you can change the arguments of the find method for match case and whole cell etc etc

[Zvoni]

I know of 2 approaches (well, 3 with Pete's idea)
1) Load the Data from your worksheet into an array, and populate your ListBox from that Array using the List-Property
In that way you could search the Array instead of the ListBox

2) AND THIS IS UNTESTED! - Make a Loop through your columns, changing the TextColumn-Property of the Listbox and use SendMessage LB_FINDSTRING
Untested because i don't know if SendMessage LB_FINDSTRING looks only in the first column or if it looks in the Column declared as the textColumn
Aircode

Dim i As Long
Dim Result As Long
For i=1 To ColumnCount 
ListBox.TextColumn=i
Result=SendMessage(ListBox.hWnd, LB_FINDSTRING etc.) .... 'look up the parameters
If Result>-1 Then Exit For 'If Successful SendMessage returns the ListIndex
Next
'DoSomething with Result/ListIndex

[hrzagi]

Thank you both guys. I tried westconn1 solution and it works, now i could search by any criteria. But it would be very helpful if it clear listbox and left only results that contain search terms and after that if i could select result that i need from the listbox and edit that result in textboxes. I`m very new in VBA so its almost immpossible for me to write code when you just explain it to me and i would be very thankful if you guys could write whole code like westconn1 did. So far all code you see I scraped from internet tutorials. I will not earn any money from this program, it will just make my life much easier on my job but i could give you few bucks for a beer if you help me to finish the program

[westconn1]

to repopulate the list box with a returned list of matching results, i would suggest using an SQL query on the worksheet, then populating the listbox with the returned recordset

[westconn1]

here is a sample code to do as suggested above, this is tested on your sample workbook and works as intended, requires a reference to ADO (microsoft activex data objects)

Private Sub cmbSearch_Click()
Dim i As Long
Dim conn As ADODB.Connection, rs As ADODB.Recordset
Dim var As String, where As String
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
conn.Open "Provider=microsoft.ace.oledb.12.0;data source=" & ThisWorkbook.FullName & ";extended properties=""excel 12.0 Xml;hdr=no"""
For i = 1 To 8
    where = where & "F" & i & " Like '%" & txtSearch & "%' or "
Next
var = "Select * from [sheet1$] where " & Left(where, Len(where) - 4)
rs.Open var, conn, adOpenStatic
rs.MoveFirst
With lstDisplay
    .RowSource = ""
    .Clear
    Do Until rs.EOF
            .AddItem rs(0) & vbNullString
            For i = 1 To .ColumnCount - 1
                .List(.ListCount - 1, i) = rs(i) & vbNullString
            Next
        rs.MoveNext
    Loop
End With
rs.Close
conn.Close

End Sub

[hrzagi]

Thanx, I will try your code as soon as possible.

[hrzagi]

here is a sample code to do as suggested above, this is tested on your sample workbook and works as intended, requires a reference to ADO (microsoft activex data objects)

Private Sub cmbSearch_Click()
Dim i As Long
Dim conn As ADODB.Connection, rs As ADODB.Recordset
Dim var As String, where As String
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
conn.Open "Provider=microsoft.ace.oledb.12.0;data source=" & ThisWorkbook.FullName & ";extended properties=""excel 12.0 Xml;hdr=no"""
For i = 1 To 8
    where = where & "F" & i & " Like '%" & txtSearch & "%' or "
Next
var = "Select * from [sheet1$] where " & Left(where, Len(where) - 4)
rs.Open var, conn, adOpenStatic
rs.MoveFirst
With lstDisplay
    .RowSource = ""
    .Clear
    Do Until rs.EOF
            .AddItem rs(0) & vbNullString
            For i = 1 To .ColumnCount - 1
                .List(.ListCount - 1, i) = rs(i) & vbNullString
            Next
        rs.MoveNext
    Loop
End With
rs.Close
conn.Close

End Sub

Works like a charm Thank you very much for your effort. Only thing that doesnt work is populating textboxes after i click on result that i want to change or edit and without that the program is useless to me. I know that you have already done so much for me but could you please solve me that?

[westconn1]

populating the textboxes from a listitem is really easy, the harder part is to know which row to edit
as the listbox rows are no longer related to the data rows
are any of the columns in the data a unique value to each row?
or in the worksheet, can you have a column for the row number of the data? which will have to be updated anytime a row is deleted

[hrzagi]

can you have a column for the row number of the data? which will have to be updated anytime a row is deleted

First column is the ordinal number of the document (data) and its always unique in each row. (In example data its not unique but in real world use it will be).
I dont see a problem if i add one more column if needed.

[westconn1]

In example data its not unique but in real world use it will be

that can be made to work

try like

Private Sub lstDisplay_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim fnd As Range
Set fnd = Range("a:a").Find(lstDisplay.Text)
updateRow = fnd.Row
txtRedniBroj.Text = fnd
txtNazivPismena.Text = fnd.Offset(, 1)
txtBrojPismena.Text = fnd.Offset(, 2)
txtPosaljitelj.Text = fnd.Offset(, 3)
txtDatumZaprimanja.Text = fnd.Offset(, 4)
txtDatumRazduzenja.Text = fnd.Offset(, 5)
cmbPolicajac.Text = fnd.Offset(, 6)
cmbStatus.Text = fnd.Offset(, 7)

End Sub

Private Sub cmdUpdate_Click()
Dim cel As Range
Set cel = Cells(updateRow, 1)
cel = txtRedniBroj.Text
cel.Offset(, 1) = txtNazivPismena.Text
cel.Offset(, 2) = txtBrojPismena.Text
cel.Offset(, 3) = txtPosaljitelj.Text
cel.Offset(, 4) = txtDatumZaprimanja.Text
cel.Offset(, 5) = txtDatumRazduzenja.Text
cel.Offset(, 6) = cmbPolicajac.Text
cel.Offset(, 7) = cmbStatus.Text

End Sub

i thought double click was more appropriate, but you can please yourself, if you prefer click

i would probably leave the textboxes empty until some item was selected

[Zvoni]

If it's really a ListBox (and not a ListView), why not just assigning the Row-No from Excel.Cell to ItemData during inital populating?
That way you can use the Click-Event, read the Row from ItemData at the SelectedItem, and voila: you have everything you need.

[westconn1]

If it's really a ListBox (and not a ListView)

msforms2 multicolumn listbox, afaik no itemdata
once the listbox is populated from a subset of the data (as reqested), there is no reference to worksheet row, unless i am missing some easier method, i have to search the worksheet for the row to update either before or after loading the textboxes

[Zvoni]

msforms2 multicolumn listbox, afaik no itemdata
once the listbox is populated from a subset of the data (as reqested), there is no reference to worksheet row, unless i am missing some easier method, i have to search the worksheet for the row to update either before or after loading the textboxes

Ah, in that case: Why not just use an invisible last column (Width=0) as your own "ItemData"?

[westconn1]

i did consider that, but as the first column is unique data the find method seemed adequate

[Zvoni]

I'm an idiot: Question to the OP: Does your sheet have an Unique Identifier per Row? Kind of like a Primary Key? At a guess maybe your first Column in your Excel-Sheet?

Because if you have, we could you use a combination of built-in worksheet functions to retrieve the Row-Number (Combination of INDEX and MATCH)

[hrzagi]

I'm an idiot: Question to the OP: Does your sheet have an Unique Identifier per Row? Kind of like a Primary Key? At a guess maybe your first Column in your Excel-Sheet?

Because if you have, we could you use a combination of built-in worksheet functions to retrieve the Row-Number (Combination of INDEX and MATCH)

Every row have unique identifier in first column because first colum is ordinal number. I tried westconn1 code and it works great. Only problem is that all dates in column 5 and 6 get messed up if i edit anythinh after i search for row and i edit searched result. When i restart userform the dates are shown correct. Example date 15.03.2019 is shown as 43525.

[Zvoni]

Every row have unique identifier in first column because first colum is ordinal number. I tried westconn1 code and it works great. Only problem is that all dates in column 5 and 6 get messed up if i edit anythinh after i search for row and i edit searched result. When i restart userform the dates are shown correct. Example date 15.03.2019 is shown as 43525.

Formatting Problem!

[westconn1]

43525 should be 1st march 2019

are the dates messed p in the userform or worksheet?
my testing did not produce the messed up results
all of the dates on the sample worksheet are in the format 15/03/2019

try

Private Sub cmdUpdate_Click()
Dim cel As Range
Set cel = Cells(updateRow, 1)
cel = txtRedniBroj.Text
cel.Offset(, 1) = txtNazivPismena.Text
cel.Offset(, 2) = txtBrojPismena.Text
cel.Offset(, 3) = txtPosaljitelj.Text
cel.Offset(, 4) = CDate(txtDatumZaprimanja.Text)
cel.Offset(, 5) = CDate(txtDatumRazduzenja.Text)
cel.Offset(, 6) = cmbPolicajac.Text
cel.Offset(, 7) = cmbStatus.Text

End Sub

an empty date textbox will result in an error, with cdate, as your locale settings are different to mine, you will have to do some testing yourself

[hrzagi]

Its solved, it was stupid error because dates in worksheet wasnt all in same format, after i changed that all work great. Thanks alot

[hrzagi]

Is it possible to add one more thing to search code. It would be great if there is message "No results" if nothing is find after search.

[westconn1]

try like

Private Sub cmbSearch_Click()
Dim i As Long
Dim conn As ADODB.Connection, rs As ADODB.Recordset
Dim var As String, where As String
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
conn.Open "Provider=microsoft.ace.oledb.12.0;data source=" & ThisWorkbook.FullName & ";extended properties=""excel 12.0 Xml;hdr=no"""
For i = 1 To 8
    where = where & "F" & i & " Like '%" & txtSearch & "%' or "
Next
var = "Select * from [sheet1$] where " & Left(where, Len(where) - 4)
rs.Open var, conn, adOpenStatic
If rs.RecordCount > 0 Then
    rs.MoveFirst
    With lstDisplay
        .RowSource = ""
        .Clear
        Do Until rs.EOF
                .AddItem rs(0) & vbNullString
                For i = 1 To .ColumnCount - 1
                    .List(.ListCount - 1, i) = rs(i) & vbNullString
                Next
            rs.MoveNext
        Loop
    End With
Else
    MsgBox "No results"
End If
rs.Close
conn.Close

End Sub

[hrzagi]

After testing program this is the problem I find. Before using search everything works fine, if I add data or change something its immediately shown in listbox. After I search for some data and return to main display where all data is shown if I try to add new data or change existing its not shown in listbox but data is added to sheet. If I want to see changes or added data I need to restart userform or i need to press search button with empty search box.
Also, I formated column 5 and 6 to show dates but new added dates from userform dont get recognized as dates and they are aligned left in column and they are not shown in userform listbox. When I format new added dates in column to be recognized by excell as dates then again everything works fine but I need to format every new added date. I attached exceel file with all changes you suggested above. https://www.dropbox.com/s/j6ug72bgpa...0v.4.xlsm?dl=0

[westconn1]

you can force the listbox to update at any time, by setting the rowsource of the listbox, as it does in userform initialize, up to you to determine when you want it to happen, or as you already know any empty search

with the dates, as i already stated previously, you need to experiment, to find what works correctly, on testing they worked correctly for me with cdate

[hrzagi]

I added " Call UserForm_Initialize " after every command button sub and now it works. Thanx

[westconn1]

you could have just put the code in a separate sub then call it from any place