Userform + listbox question

[marmo]

Hi again ,
In an earlier mail i asked about filtering a listbox.
I think i did not explain things clearly enough.
Therefor i'll try again in this post:

I have a little application in Excel with several sheets.
One of the sheets contains client data such as name (columnA), address
(columnB) , etc.....
This range is called DbClients
On a Userform i have a listbox which has the DBClients as rowsource

What i'd like to do is to put a textbox above the listbox to filter the data
in the listbox.
When i type "Carl" in the textbox , the listbox should only show the records
with the name "Carl"

Is there a way to do this or is it to complicated.
I'm using Excel 2000-2002
If not possible, please also reply.
If more info needed , don't hesitate to reply.

Many thanks
MarMo

 

[Bob Phillips]

It can be done, but two questions.

Is the name Carl the complete value in the cells, or part, and what column
is the name in?

What do you mean by load the listbox with the records? Is that the names or
some other column?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[marmo]

Hi Bob ,
The name "Carl" could be a part of a value in the cells , and the name is in
column B
Here is an example of the cells layout:
A B C D
E
1 Code Name Address Zipcode
Place
2 326 Carl Lewis Highstreet 23 88620
Paris
2 366 Rowan Atkin LevelStreet 6 99823
Nice
3 633 Carlisle Francs Burbonlane 5 9983 CA
Amsterdam
the complete range has the name DBClients
(ActiveWorkbook.Names.Add Name:="DBClients", RefersToR1C1:= _
"=Sheet1!R1C1:R4C5")
When the userform is loaded , the listbox is automaticly filled with the
DBClients data, which is the complete range.
In this example all 5 colums and all rows within the range.name DBClients
are included .

So if i put "Carl" in the text box , the listbox should show me "Carl Lewis"
and "Carlisle Francs"

Hope this anwers your suestion.
If you need a real example , please let me know.

Thanks in advance.
MarMo

 

[Bob Phillips]

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim cell As Range
Dim rng As Range
Dim i As Long

With Me.ListBox1
.RowSource = ""
.Clear
For Each cell In Range("DBClients").Columns(2).Cells
If InStr(cell.Value, Me.TextBox1.Value) Then
.AddItem Range("DBClients").Cells(cell.Row, 1)
For i = 2 To 3
.List(.ListCount - 1, i - 1) =
Range("DBClients").Cells(cell.Row, i)
Next i
End If
Next cell
End With

End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[MarMo]

Hi Bob ,

Thanks a lot for this.
It works great. I only need to finetune the code to my needs.
Again , thanks a lot.
MarMo