Text box to list box population

[likexl]

Hi,

I want to know that : "Is it possible to populate a list box from one column in worksheet, as we start typing in the text box. As the letters are typed the list box should be updated with the most relevant match"

If it is possible, what is the basic approch to be started.

Excel vba, office 2007, win 7.

Thanks.

 

[likexl]

Hi all,

Thanks for reading this post.

I was confused regarding this matter. Thanks Gooooooo.. for directing me towards some tips.

I had arrived to the solution to dynamically update list box using text box typing.
I am posting this code for any one who find it useful. Any suggestion or improvements are invited.

code:

Private Sub TextBox1_Change()

ListBox1.Clear

Application.Goto Reference:=Worksheets("sheet1").Range("a2")

Dim NumCounta As Long
NumCounta = Cells(Rows.Count, "A").End(xlUp).Row

Dim qry As String
    
    For i = NumCounta To 2 Step -1
    qry = TextBox1.Value
    If ActiveCell.Value Like qry & "*" Then
    With Me.ListBox1
    .AddItem ActiveCell.Value
    End With
    ActiveCell.Offset(1, 0).Select
    End If
    Next i

End Sub

I had first initialised list box with all items.

One question : If this loop is applied to large ranges will it affect speed. What to do to make execution fast.

 

[likexl]

Hi all,

Thanks for reading this post.

I was confused regarding this matter. Thanks Gooooooo.. for directing me towards some tips.

I had arrived to the solution to dynamically update list box using text box typing.
I am posting this code for any one who find it useful. Any suggestion or improvements are invited.

code:

Private Sub TextBox1_Change()

ListBox1.Clear

Application.Goto Reference:=Worksheets("sheet1").Range("a2")

Dim NumCounta As Long
NumCounta = Cells(Rows.Count, "A").End(xlUp).Row

Dim qry As String
    
    For i = NumCounta To 2 Step -1
    qry = TextBox1.Value
    If ActiveCell.Value Like qry & "*" Then
    With Me.ListBox1
    .AddItem ActiveCell.Value
    End With
    ActiveCell.Offset(1, 0).Select
    End If
    Next i

End Sub

I had first initialised list box with all items.

One question : If this loop is applied to large ranges will it affect speed. What to do to make execution fast.

Please note: Following is required with if.

Else
    ActiveCell.Offset(1, 0).Select