torquil
05-14-2020, 03:04 AM
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.
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.