Listbox filter and display issues

**brouhaha501** · 06-07-2010, 03:56 PM

I think I might have gotten a little bit over my head with a project for work. I’m 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’m completely stuck. Is this something that just cannot be done, or am I just missing something REALLY important???

**brouhaha501** · 06-07-2010, 03:57 PM

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

Thread: Listbox filter and display issues

Thread Tools

Display

Listbox filter and display issues

Re: Listbox filter and display issues

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions