dcsimg
Results 1 to 27 of 27

Thread: [RESOLVED] Search multiple columns in userform and edit results

  1. #1

    Thread Starter
    New Member
    Join Date
    Nov 2019
    Posts
    10

    Resolved [RESOLVED] Search multiple columns in userform and edit results

    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

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,976

    Re: Search multiple columns in userform and edit results

    Are you using the RowSource or List-Property of the Listbox to populate your Listbox?
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  3. #3
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,563

    Re: Search multiple columns in userform and edit results

    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
    Code:
    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
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,976

    Re: Search multiple columns in userform and edit results

    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
    Code:
    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
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  5. #5

    Thread Starter
    New Member
    Join Date
    Nov 2019
    Posts
    10

    Re: Search multiple columns in userform and edit results

    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

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,563

    Re: Search multiple columns in userform and edit results

    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
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,563

    Re: Search multiple columns in userform and edit results

    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)

    Code:
    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
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  8. #8

    Thread Starter
    New Member
    Join Date
    Nov 2019
    Posts
    10

    Re: Search multiple columns in userform and edit results

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

  9. #9

    Thread Starter
    New Member
    Join Date
    Nov 2019
    Posts
    10

    Re: Search multiple columns in userform and edit results

    Quote Originally Posted by westconn1 View Post
    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)

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

  10. #10
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,563

    Re: Search multiple columns in userform and edit results

    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
    Last edited by westconn1; Nov 11th, 2019 at 03:08 PM.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  11. #11

    Thread Starter
    New Member
    Join Date
    Nov 2019
    Posts
    10

    Re: Search multiple columns in userform and edit results

    Quote Originally Posted by westconn1 View Post
    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.
    Last edited by hrzagi; Nov 11th, 2019 at 04:53 PM.

  12. #12
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,563

    Re: Search multiple columns in userform and edit results

    In example data its not unique but in real world use it will be
    that can be made to work

    try like

    Code:
    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
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  13. #13
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,976

    Re: Search multiple columns in userform and edit results

    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.
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  14. #14
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,563

    Re: Search multiple columns in userform and edit results

    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
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  15. #15
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,976

    Re: Search multiple columns in userform and edit results

    Quote Originally Posted by westconn1 View Post
    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"?
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  16. #16
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,563

    Re: Search multiple columns in userform and edit results

    i did consider that, but as the first column is unique data the find method seemed adequate
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  17. #17
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,976

    Re: Search multiple columns in userform and edit results

    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)
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  18. #18

    Thread Starter
    New Member
    Join Date
    Nov 2019
    Posts
    10

    Re: Search multiple columns in userform and edit results

    Quote Originally Posted by Zvoni View Post
    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.

  19. #19
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,976

    Re: Search multiple columns in userform and edit results

    Quote Originally Posted by hrzagi View Post
    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!
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  20. #20
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,563

    Re: Search multiple columns in userform and edit results

    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
    Code:
    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
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  21. #21

    Thread Starter
    New Member
    Join Date
    Nov 2019
    Posts
    10

    Re: Search multiple columns in userform and edit results

    Its solved, it was stupid error because dates in worksheet wasnt all in same format, after i changed that all work great. Thanks alot
    Last edited by hrzagi; Nov 13th, 2019 at 03:08 AM.

  22. #22

    Thread Starter
    New Member
    Join Date
    Nov 2019
    Posts
    10

    Re: Search multiple columns in userform and edit results

    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.

  23. #23
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,563

    Re: [RESOLVED] Search multiple columns in userform and edit results

    try like
    Code:
    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
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  24. #24

    Thread Starter
    New Member
    Join Date
    Nov 2019
    Posts
    10

    Re: [RESOLVED] Search multiple columns in userform and edit results

    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

  25. #25
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,563

    Re: [RESOLVED] Search multiple columns in userform and edit results

    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
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  26. #26

    Thread Starter
    New Member
    Join Date
    Nov 2019
    Posts
    10

    Re: [RESOLVED] Search multiple columns in userform and edit results

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

  27. #27
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,563

    Re: [RESOLVED] Search multiple columns in userform and edit results

    you could have just put the code in a separate sub then call it from any place
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width