Searchable listbox - Multi column - additem to second column only works first time

[torquil]

Hi everyone, strange thing happening with a list box on a userform i have.

I have a dropdown listbox apear as you type into a text box, what i want is for it to run the search and display 2 columns (a code, name of searched item). Here is the code i am using:



Private Sub tbSymbol_Change()
' Searchable List box for Symbol
Dim i As Integer

lbSymbol.Clear
lbSymbol.Visible = True
lbSymbol.ColumnCount = 2
lbSymbol.ColumnWidths = "50,120"

With Worksheets("Live Share Data")
For i = 2 To .Range("A4000").End(xlUp).Row
If UCase(Left(.Cells(i, 1), Len(tbSymbol.Text))) = UCase(tbSymbol.Text) Then
lbSymbol.AddItem .Cells(i, 1)
lbSymbol.List(lbSymbol.ListCount - 1, 1) = Cells(i, 2)
End If
Next i
End With
End Sub


So when i make the line "lbSymbol.List(lbSymbol.ListCount - 1, 1) = Cells(i, 2)" active and run the code it works perfectly then if I clsoe the userform and reopen then i only shows the first column in the listbox.
thoughts? guessing there is something small here i am messing up.

[snb]

In this case the textbox is redundant.

All properties of a listbox should be set in design mode.
Only the populating of a listbox has to be done in the Userform_Initialize event, using the listbox's .List property.

See https://www.snb-vba.eu/VBA_Fill_combobox_listbox_en.html

[torquil]

Sorry I dont follow you snb, I want the textbox there for user ease and that the listbox apears and disaperar once a selection is made. any ideas why it does not work more than once?

[snb]

In that case: please read again.

[torquil]

Anybody out there able to help?

[macropod]

Cross-posted at:
https://www.mrexcel.com/board/threads/userform-listbox-2-columns-only-wokring-when-code-first-entered.1134354/
https://www.excelforum.com/excel-programming-vba-macros/1316389-vba-userform-listbox-2-colums-only-working-once.html
Please read VBA Express' policy on Cross-Posting in Rule 3: http://www.vbaexpress.com/forum/faq.php?faq=new_faq_item#faq_new_faq_item3