Solved: Search 2nd ListBox Column

[tccmdr]

Gurus,

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

[VBA]
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
[/VBA]

[mikerickson]

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]

I assumed you meant

[VBA]

Me.ListBox1.BoundColumn = 2

[/VBA]

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 and that's all I want to see in the listbox

Your thoughts....

[mikerickson]

I don't think you need to reset any of the properties of the ListBox other than .BoundColumn.

[tccmdr]

Bound Column stays where it is

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

[mikerickson]

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]

I thought .TextColumn could only be True or False

[mikerickson]

The help system seems to imply that it will take a column value. The Object Browser lists it as type Variant not Boolean.

[tccmdr]

It excepted .TextColumn = 2,

but the search didn't work

[mikerickson]

When you substitute a MsgBox for the If statement, does it loop through the entrys you want to check?

[tccmdr]

I can't send an attachment from this PC.


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