Userform - need help on code for searching on listbox values

[Zakkala]

Hi there,

I have a multipage userform. One page is for searching the excel database, one for adding entries to the database, one for amending them and the final to contain the search results. My forms to add and amend work okay.

The search form has 3 list boxes, each of which can have multiple options chosen.

I need to write some code that will search the database as follows, and return the data from the resulting rows into the listbox on the search results page of the form. The database has data in Columns A to N.

I want the search to look for rows of data where the options chosen in listbox1 match Column A of the data, the listbox2 options match Column G and listbox3 options match Column H. I would like the data from any rows that match all three criteria returning in the listbox on the search results page, and if there are no rows of data that match all these options, I need one row in the listbox saying 'no entries match your search request' or something similar.

To further complicate matters, I would then like to be able to choose one of the rows of data returned in the results listbox, and use it to populate the 'amend entry' userform!

This is the search code I'm trying to modify to suit my needs (found elsewhere on this site), but don't know if I'm on the right track or how to convert it from textbox to listbox - can anyone help please?

Private Sub findbutton_click()
 
 'Find all matching wines
    
    Dim FirstAddress As String
    Dim strFind As String    'what to find
    Dim rSearch As Range     'range to search
    Dim fndA, fndB, fndC, fndD, fndE, fndF, fndG, fndH, fndI, fndJ, fndK, fndL, fndM As String
    Dim head1, head2, head3, head4, head5, head6, head7, head8, head9, head10, head11, head12, head13 As String    'headings for list
    Dim i As Integer
    i = 1
    Set rSearch = Sheet1.Range("a2", Range("a65536").End(xlUp))
    strFind = Me.TextBox2.Value
    With rSearch
        Set c = .Find(strFind, LookIn:=xlValues)
       
       
        If Not c Is Nothing Then    'found it
            c.Select
           
            'load the headings
            head1 = Range("a1").Value
            head2 = Range("b1").Value
            head3 = Range("c1").Value
            head4 = Range("d1").Value
            head5 = Range("e1").Value
            head6 = Range("f1").Value
            head7 = Range("g1").Value
            head8 = Range("h1").Value
            head9 = Range("j1").Value
            head10 = Range("k1").Value
            head11 = Range("l1").Value
            head12 = Range("m1").Value
            head13 = Range("n1").Value
                        
            With Me.ListBox1
                MyArray(0, 0) = head1
                MyArray(0, 1) = head2
                MyArray(0, 2) = head3
                MyArray(0, 3) = head4
                MyArray(0, 4) = head5
                MyArray(0, 5) = head6
                MyArray(0, 6) = head7
                MyArray(0, 7) = head8
                MyArray(0, 8) = head9
                MyArray(0, 9) = head10
                MyArray(0, 10) = head11
                MyArray(0, 11) = head12
                MyArray(0, 12) = head13
                          
            End With
            
            FirstAddress = c.Address
            
            Do
                'Load details into Listbox
                fndA = c.Value
                fndB = c.Offset(0, -1).Value
                fndC = c.Offset(0, 1).Value
                fndD = c.Offset(0, 2).Value
                fndE = c.Offset(0, 3).Value
                fndF = c.Offset(0, 4).Value
                fndG = c.Offset(0, 5).Value
                fndH = c.Offset(0, 6).Value
                fndI = c.Offset(0, 7).Value
                fndJ = c.Offset(0, 8).Value
                fndK = c.Offset(0, 9).Value
                fndL = c.Offset(0, 10).Value
                fndM = c.Offset(0, 11).Value
               

                MyArray(i, 0) = fndA
                MyArray(i, 1) = fndB
                MyArray(i, 2) = fndC
                MyArray(i, 3) = fndD
                MyArray(i, 4) = fndE
                MyArray(i, 5) = fndF
                MyArray(i, 6) = fndG
                MyArray(i, 7) = fndH
                MyArray(i, 8) = fndI
                MyArray(i, 9) = fndJ
                MyArray(i, 10) = fndK
                MyArray(i, 11) = fndL
                MyArray(i, 12) = fndM
               
                i = i + 1
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> FirstAddress
Else: MsgBox strFind & "  not in use"    'search failed
        End If
    End With
    'Load data into LISTBOX
    Me.SearchResults.List() = MyArray
        With Me

End With


End Sub

 

[Norie]

So does the first listbox take precedence?

ie you look in column A for what's in listbox1 first

How are the listboxes populated?

Are there repeating values in the columns you want to search?

PS If you wanted to use that code then you could probably just need to change Textbox2 here to the listbox name

strFind = Me.TextBox2.Value

But that would only do the initial search.

You would then need to see if the other columns matched the other listboxes in that record.

Also if you want to allow the user amend the record you'll need to store the row it's on somewhere.

You might already have something for that though.

 

[Zakkala]

Hi Norie.

Yes, the first listbox would take precedence.

The first two listboxes each have 5 options, which are populated from the initialize sub. The third listbox has an unspecified number of options based on the different options in the database in Column G - it is populated as part of the initialize sub too. The user can choose any number of options in each listbox.

There will be repeated values in the columns, yes.

And no, I don't have anything to store the row number yet - knew I'd need it, but I'm just not very good at this VB lark.

 

[Norie]

Zakkala

My initial thoughts are to use advanced filter.

In code you can put the results from that on another sheet.

So I was thinking this.

1 Create a new sheet to hold the criteria which will come from the listboxes.

2 Create another new sheet to hold the results of the advanced filter.

3 Execute the advanced filter and populate the results sheet.

4 Populate the results listbox from the results sheet.

5 Delete both new sheets.

I've not really tried anything like that before, but in theory, to me anyway, it sounds as though it would work.

As to it being a practical solution that I don't know - I'm going to go off and see if I can set something up.

As to the row number issue, I don't think that's to hard a problem.

My idea for that would be to add a simple formula like =ROW() to the data.

This could then be included in the advanced filter and populate a (hidden) column in the results listbox.

And it could then be used when writing the amended data back to the worksheet.

At the moment I'm just thinking of the top of my head but I'll try and post back with something a little more concrete.

 

[Zakkala]

Thanks Norie.

I'll continue to mess about and see if I come up with anything useful myself!