I recommend a different approach where the search criteria is entered
via an InputBox rather than stored on the sheet so programtic control
is easier to manage/maintain. You could use a combobox on a userform if
you want to control user selection, though!
That said, I made a data table with 10 rows and 7 cols, then ran the
following code in the button Click event.
Private Sub btnClientSearch_Click()
Dim vDataIn, n&, k&, Ndx&, vAns
vAns = InputBox("Enter the client ID to search for")
If vAns = Empty Then Exit Sub
vDataIn = Me.UsedRange
With Me.lstClientSearch
.Clear: .ColumnCount = UBound(vDataIn, 2)
For n = LBound(vDataIn) To UBound(vDataIn)
If vDataIn(n, 2) = vAns Then
.AddItem vDataIn(n, 1)
For k = 0 To UBound(vDataIn, 2) - 1
.List(Ndx, k) = vDataIn(n, k + 1)
Next 'k
Ndx = Ndx + 1
End If
Next 'n
End With
End Sub
Note that I used my own naming convention for the control type prefix.
You can change this to suit your preference OR rename your controls
accordingly.
--
Garry
Free uenet access at
http://www.eternal-september.org
Classic
VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.
vb.general.discussion