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
Bookmarks