Thread: Listbox filter and display issues

here is the second part:

Option Explicit
Dim MyArray(2, 35)
Public MyData As Range, c As Range
Dim rFound     As Range
Dim r          As Long
Dim rng        As Range

Private Sub UserForm_Initialize()
   Dim wks As Worksheet
   Dim vaItems As Variant
    Dim I As Long, j As Long
    Dim vTemp As Variant
    
   If Sheet1.AutoFilterMode = True Then
   Sheet1.AutoFilterMode = False
   End If
   
    Set MyData = Sheet1.Range("a2").CurrentRegion   'database
    With Me
        .Caption = "Database Example"    'userform caption
 End With

Set wks = Sheets("sheet1")
Me.textbox1.List = wks.Range("A2", wks.Range("A65536").End(xlUp)).Value
Me.cmbfind2.Visible = False
Me.cmbFindAll.Visible = True
comboset.List = Array("Open", "Close")
comboda.List = Array("Leavy", "McConnaughhay", "Rissman")
comboresult.List = Array("n/a", "Yes", "No")
combosettle.List = Array("n/a", "Yes", "No")
combostatus.List = Array("Unresolved", "O/C Dismissed", "We agreed")
combodepo.List = Array("", "Yes", "No")
combodrdep.List = Array("", "Yes", "No")
DCSCR.List = Array("n/a", "Yes", "No")
combotime.List = Array("n/a", "Yes", "No")
CESCR.List = Array("", "Yes", "No")
End Sub

Sub cmbFindAll_Click()
    Dim strFind As String    'what to find
    Dim rFilter As Range     'range to search
    Set rFilter = Sheet1.Range("a2", Range("ai65536").End(xlUp))
    Set rng = Sheet1.Range("a2", Range("a65536").End(xlUp))
    strFind = Me.textbox1.Value
    With Sheet1
        If Not .AutoFilterMode Then .Range("A2").AutoFilter
        rFilter.AutoFilter Field:=1, Criteria1:=strFind
        Set rng = rng.Cells.SpecialCells(xlCellTypeVisible)
        Me.ListBox1.Clear
        For Each c In rng
            With Me.ListBox1
                .AddItem c.Value
                .List(.ListCount - 1, 1) = c.Offset(0, 3).Value
                .List(.ListCount - 1, 2) = c.Offset(0, 7).Value
                .List(.ListCount - 1, 3) = c.Offset(0, 1).Value
                .List(.ListCount - 1, 4) = c.Offset(0, 2).Value
                .List(.ListCount - 1, 5) = c.Offset(0, 4).Value
                .List(.ListCount - 1, 6) = c.Offset(0, 5).Value
                .List(.ListCount - 1, 7) = c.Offset(0, 6).Value
                .List(.ListCount - 1, 8) = c.Offset(0, 8).Value
                .List(.ListCount - 1, 9) = c.Offset(0, 9).Value
                .List(.ListCount - 1, 11) = c.Offset(0, 11).Value
                .List(.ListCount - 1, 12) = c.Offset(0, 12).Value
                .List(.ListCount - 1, 13) = c.Offset(0, 13).Value
                .List(.ListCount - 1, 14) = c.Offset(0, 14).Value
                .List(.ListCount - 1, 15) = c.Offset(0, 15).Value
                .List(.ListCount - 1, 16) = c.Offset(0, 16).Value
                .List(.ListCount - 1, 17) = c.Offset(0, 17).Value
                .List(.ListCount - 1, 18) = c.Offset(0, 18).Value
                .List(.ListCount - 1, 19) = c.Offset(0, 19).Value
                .List(.ListCount - 1, 20) = c.Offset(0, 20).Value
                .List(.ListCount - 1, 21) = c.Offset(0, 21).Value
                .List(.ListCount - 1, 22) = c.Offset(0, 22).Value
                .List(.ListCount - 1, 23) = c.Offset(0, 23).Value
                .List(.ListCount - 1, 24) = c.Offset(0, 24).Value
                .List(.ListCount - 1, 25) = c.Offset(0, 25).Value
                .List(.ListCount - 1, 26) = c.Offset(0, 26).Value
                .List(.ListCount - 1, 27) = c.Offset(0, 27).Value
                .List(.ListCount - 1, 28) = c.Offset(0, 28).Value
                .List(.ListCount - 1, 29) = c.Offset(0, 29).Value
                .List(.ListCount - 1, 30) = c.Offset(0, 30).Value
                .List(.ListCount - 1, 31) = c.Offset(0, 31).Value
                .List(.ListCount - 1, 32) = c.Offset(0, 33).Value
                .List(.ListCount - 1, 33) = c.Offset(0, 34).Value
            End With
        Next c
    End With
    Me.cmbfind2.Visible = True
    Me.cmbFindAll.Visible = False
End Sub

Private Sub cmbSelect_Click()
    If Me.ListBox1.ListIndex = -1 Then    'not selected
        MsgBox " No selection made"
    ElseIf Me.ListBox1.ListIndex >= 0 Then    'User has selected
        r = Me.ListBox1.ListIndex
        With Me
            .txtclaim.Value = ListBox1.List(r, 0)
            .txtadj.Value = ListBox1.List(r, 1)
            .txtfname.Value = ListBox1.List(r, 2)
            .txtlname.Value = ListBox1.List(r, 3)
            .txtdoi.Value = ListBox1.List(r, 4)
            .txtda.Value = ListBox1.List(r, 5)
            .comboda.Value = ListBox1.List(r, 6)
            .txtpfbrcvd.Value = ListBox1.List(r, 7)
            .txtpfbresp.Value = ListBox1.List(r, 8)
            .txtissues.Value = ListBox1.List(r, 9)
            .txtresponse.Value = ListBox1.List(r, 11)
            .DCSCR.Value = ListBox1.List(r, 12)
            .combotime.Value = ListBox1.List(r, 13)
            .CESCR.Value = ListBox1.List(r, 14)
            .txtmeddate.Value = ListBox1.List(r, 15)
            .combostatus.Value = ListBox1.List(r, 16)
            .txtpreconf.Value = ListBox1.List(r, 17)
            .comboresult.Value = ListBox1.List(r, 18)
            .combosettle.Value = ListBox1.List(r, 19)
            .txtpostmed.Value = ListBox1.List(r, 20)
            .combodepo.Value = ListBox1.List(r, 21)
            .txtdepo.Value = ListBox1.List(r, 22)
            .txtpredep.Value = ListBox1.List(r, 23)
            .combodrdep.Value = ListBox1.List(r, 24)
            .txtdrdepo.Value = ListBox1.List(r, 25)
            .txtpredr.Value = ListBox1.List(r, 26)
            .txtpretrial.Value = ListBox1.List(r, 27)
            .txtfinal.Value = ListBox1.List(r, 28)
            .txtprefinal.Value = ListBox1.List(r, 29)
            .txtconfops.Value = ListBox1.List(r, 30)
            .txtfhresults.Value = ListBox1.List(r, 31)
            .comboset.Value = ListBox1.List(r, 33)
            .txtnotes.Value = ListBox1.List(r, 34)
            .cmbAmend.Enabled = True      'allow amendment or
            .cmbDelete.Enabled = True     'allow record deletion
        End With
    End If
End Sub

bump - no response

I apologize if the description above is a bit too much, or may not make sense, so i will attempt to clarify.

I have a worksheet where one person on the list may have multiple rows of data. I am trying to create a userform to first search all claims with ther persons reference number, and display all rows of data in a listbox.

Then, when one of the items in the listbox is clicked, i would like to have multiple textboxes fill with the data from that specific row.

I first attempted used a combobox where you can type in the reference number you were looking for, then it would populate the listbox with the correct results. but, when you clicked on one of the items in the listbox, it would not populate the row that you wanted to see. Instead, it populates the first row of data on the worksheet, and go down from there (i.e 1st, 2nd, 3rd ect).

My second attempt I used RoyUK's example found on http://www.excel-it.com/vba_examples.htm and tried to tweak it to fit this worksheet. It works excellant, with the exception tha the textboxes fill from the listbox, and only 9 columns can go in a listbox. both of the coding is posted higher up on this same thread.

I'm in a VERY bad way right now as I really need to solve this problem soon for work. Any and all help is appreciated more than I can put into words. Please please please, if you could help!!!

I have attached the two different examples.

Thank you so much for helping out!! I'm still having an issue though:

When i type in a reference number into the combobox (userfilter), the listbox shows all of the filtered results of all of the rows with the specified info in it. but when i click on one of the items in the listbox, the textboxes to the right will populate from the first row in the sheet.

For instance - if you type in the reference number 5886410 into the combobox, the listbox will show two dates, one for each row that has the reference number in it. but when you click on the top date (02/02/2010) it will reference the very top row for Brendan Brendan instead of test sixteen, which is who the reference number is for.

I think what's happening is the code for the listbox is pulling from the sheet as a whole instead of pulling the information from that particular row, but i have no clue how to fix it.

Romper, thanks so much again for all of your help. it's really been awesome of you to take a look at this for me, and i do appreciate you taking the time to lock down the coding.

You ARE AWESOME!!!! thanks so much. it does EXACTLY what i need it to do!!!