Advice on List Box Search

[kirkm]

Hi,

I'm using a list box with multi columns as a method of display.
The list box has a tabstrip and it's click event sorts by that column.
The list box source is a worksheet.

As the columns are sorted I'd like to keep the selected item
highlighted.

The following is done before the sort.

x = List1.ListIndex
List1.BoundColumn = 2
m$ = List1
List1.BoundColumn = 3
m$ = m$ & List1
List1.BoundColumn = 4
m$ = m$ & List1

After the sort I'd like to set list index to the 'same' row.

What is the best way to do this? I can only think of
a line by line search looking for a match for m$. But this
doesn't seem very efficient. I may also be doing this all
completely the wrong way.

Thanks - Kirk

 

[Bob Phillips]

You could try something like this.

Add a further column to your data and just add an incrementing value to each
row (1, 2, 3 etc.)

Add that extra column to the RowSource of the Listbox (but don't increase
your columncount, then it won't show).

Save the listindex before sorting, then use that to match in the new column
after sorting to calculate what the listindex should now be.

For instance, suppose you have two columns of data, add a third (C) with the
incrementing number. Then something like this in the sort routine

Private Sub cmdOK_Click()
Dim iSelected As Long
Dim iRow As Long
If Me.ListBox1.ListIndex <> -1 Then
iSelected = Me.ListBox1.ListIndex + 1
End If

With Worksheets(1)
.Columns("A:C").Sort Key1:=.Range("A1"), _
Order1:=xlAscending, _
Header:=xlNo

If Me.ListBox1.ListIndex <> -1 Then
iRow = Application.Match(iSelected, .Columns("C"), 0)
Me.ListBox1.ListIndex = iRow - 1
End If

End With

End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[kirkm]

On Sat, 17 Mar 2007 04:52:22 -0000, "Bob Phillips"

Hi Bob,

Thanks for that brilliant suggestion. I've got it working
with one small issue - about 10% of the time nothing is selected.

I added MsgBox List1.ListIndex into the last If Statement
and it does have the right row number.

Any thoughts on that ?

Thanks - Kirk

 

[Bob Phillips]

Not off the top Kirk.

It might be the data, it might be frequency, a bit hard to say. Could you
mail your workbook to me to look at, preferably with a set of repeatable
actions to demonstrate it.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[kirkm]

It might be the data, it might be frequency, a bit hard to say. Could you
mail your workbook to me to look at, preferably with a set of repeatable
actions to demonstrate it.

OK Bob. How do I mail it to you ?

Thanks - Kirk

 

[Bob Phillips]

bob dot NGs at gmail dot com

do the obvious

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)