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
Bookmarks