PDA

View Full Version : Solved: Search 2nd ListBox Column



tccmdr
06-21-2007, 05:57 PM
Gurus,

What am I missing in the following code in order to search the 2nd column of a listbox:help


Private Sub TextBox2_Change()
Dim Search As String
Dim n As Long
Dim i As Long
Dim j As Long

Search = Me.TextBox2.Text
n = Len(Me.TextBox2)
j = Me.ListBox1.ListCount - 1
For i = 0 To j
If Left(Me.ListBox1.List(i), n) = Search Then
Me.ListBox1.Selected(i) = True
Exit For
End If
Next
End Sub

mikerickson
06-21-2007, 06:47 PM
add


Me.TextBox1.BoundColumn = 2
somewhere. It will cause .List to be pulled from column 2.
Enter Bound Column in the VBE help for more details, its fairly situation dependent.

tccmdr
06-21-2007, 07:09 PM
I assumed you meant



Me.ListBox1.BoundColumn = 2


However, I then need to change my listbox properties to match, in particular my .RowSource range:(

Whilst this does the job, sort of, I think I need to redesign to a filter type form as I may have multiple items with the same name :dunno and that's all I want to see in the listbox

Your thoughts....:think:

mikerickson
06-21-2007, 07:32 PM
I don't think you need to reset any of the properties of the ListBox other than .BoundColumn.

tccmdr
06-21-2007, 07:52 PM
Bound Column stays where it is

.RowSource from A2 to B2, otherwise the listbox displays the same and the search doesn't function

mikerickson
06-21-2007, 08:12 PM
Have you looked at .TextColumn?
Just as .BoundColumn determines where .Value and .List get their values,
.TextColumn determines where .Text gets its value.

Setting .TextColumn and .BoundColumn to different columns might help you look at different parts of your list.

tccmdr
06-21-2007, 09:29 PM
I thought .TextColumn could only be True or False

mikerickson
06-21-2007, 11:09 PM
The help system seems to imply that it will take a column value. The Object Browser lists it as type Variant not Boolean.

tccmdr
06-21-2007, 11:19 PM
It excepted .TextColumn = 2,

but the search didn't work:doh:

mikerickson
06-21-2007, 11:34 PM
When you substitute a MsgBox for the If statement, does it loop through the entrys you want to check?

tccmdr
06-21-2007, 11:51 PM
I can't send an attachment from this PC.:banghead:


When I get to the other one later tonight I'll send through the workbook
- It's a modification of the address.xls file Lucas sent -


It'll be a few hours before I can get to it:dunno