Results 1 to 10 of 10

Listbox filter and display issues

Threaded View

  1. #1
    Registered User
    Join Date
    01-27-2010
    Location
    Orlando, FL
    MS-Off Ver
    Excel 2007
    Posts
    30

    Listbox filter and display issues

    I think I might have gotten a little bit over my head with a project for work. I知 working on a Userform that pulls from an excel sheet with 35 columns. some of the rows in the column have very similar information, with the exception of 5-7 different cells.
    The first thing I was trying to do is be able to filter out the rows that are similar using a listbox and a combobox. In the Combobox, you would input the number in the cell that you are looking for. The userform would then autofilter the results into the listbox.
    The next thing I wanted to do is, once you click on one of the results in the listbox, it then shows you all of the data from that specific row.
    The first attempt filtered the rows into the listbox, but when I clicked on one of the results, it would always show me Row 1, even if the information I wanted was row 23. The code is listed below.
    Private Sub UserForm_initialize()
    Dim wks As Worksheet
    Dim vaItems As Variant
        Dim I As Long, j As Long
        Dim vTemp As Variant
        
    Set wks = Sheets("Legal tracking")
    Me.UserFilter.List = wks.Range("B2", wks.Range("B65536").End(xlUp)).Value
    
    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
    
    Private Sub UserFilter_Change()
    Dim MyList() As Variant
    Dim X As Long
    Dim Y As Long
    Dim FoundSomething As Boolean
    
    FoundSomething = False
    Y = 0
    For X = 2 To Sheets("legal tracking").Range("B" & Rows.Count).End(xlUp).Row
        If InStr(1, UCase(Sheets("legal tracking").Range("B" & X).Value), UCase(UserFilter)) > 0 Then
            FoundSomething = True
            ReDim Preserve MyList(Y)
            MyList(Y) = Sheets("legal tracking").Range("H" & X).Text
            Y = Y + 1
        End If
    Next
    If FoundSomething Then
        frmlegal.filteredlist.List = MyList
    Else
        frmlegal.filteredlist.Clear
    End If
    End Sub
    Private Sub filteredlist_Click()
     Dim n As Long
        n = filteredlist.ListIndex
        If n > -1 Then
            With Sheets("legal tracking")
                n = n + 2
                txtadj.Value = .Cells(n, 1).Value
                txtclaim.Value = .Cells(n, 2).Value
                txtlname.Value = .Cells(n, 3).Value
                txtfname.Value = .Cells(n, 4).Value
                txtdoi.Value = .Cells(n, 5).Value
                txtda.Value = .Cells(n, 6).Value
                comboda.Value = .Cells(n, 7).Value
                txtpfbrcvd.Value = .Cells(n, 8).Value
                txtpfbresp.Value = .Cells(n, 9).Value
                txtissues.Value = .Cells(n, 10).Value 
                txtresponse.Value = .Cells(n, 12).Value
                DCSCR.Value = .Cells(n, 13).Value
                combotime.Value = .Cells(n, 14).Value
                CESCR.Value = .Cells(n, 15).Value
                txtmeddate.Value = .Cells(n, 16).Value
                combostatus.Value = .Cells(n, 17).Value
                txtpreconf.Value = .Cells(n, 18).Value
                comboresult.Value = .Cells(n, 19).Value
                combosettle.Value = .Cells(n, 20).Value
                txtpostmed.Value = .Cells(n, 21).Value
                combodepo.Value = .Cells(n, 22).Value
                txtdepo.Value = .Cells(n, 23).Value
                txtpredep.Value = .Cells(n, 24).Value
                combodrdep.Value = .Cells(n, 25).Value
                txtdrdepo.Value = .Cells(n, 26).Value
                txtpredr.Value = .Cells(n, 27).Value
                txtpretrial.Value = .Cells(n, 28).Value
                txtfinal.Value = .Cells(n, 29).Value
                txtprefinal.Value = .Cells(n, 30).Value
                txtconfops.Value = .Cells(n, 31).Value
                txtfhresults.Value = .Cells(n, 32).Value
                comboset.Value = .Cells(n, 34).Value
                txtnotes.Value = .Cells(n, 35).Value
    End With
    End If
    End Sub
    The second attempt, I used an offset code to have the data pull from the lisbox instead of the spreadsheet, but found that I could only have 9 columns in the listbox, which will not populate the 34 fields that I need to populate. I will post up the second part of code seperately as all of it's too long to fit into one post

    I really need help with this as I知 completely stuck. Is this something that just cannot be done, or am I just missing something REALLY important???
    Last edited by brouhaha501; 06-14-2010 at 08:11 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1