CODEWITHSUNDEEP
excelvba
Nguyen Van Hiep
Nguyen Van Hiep

Reputation: 11

Search textbox to listbox multi columns

I have a code work perfectly, that search the items in worksheets then the userform textbox display in the listbox. but somehow it show only in one columns in listbox. I want to show 4 columns in lisbox.

Code:

Private Sub TextBox1_Change()

     Dim search As Variant 
     Dim textbox As Variant 
     Dim index As Variant 
     Dim item As Variant 
     Dim result As Variant 
     Dim match As Integer

     With Me.ListBox1 
     .RowSource = "" 
     End With

     On Error GoTo skip match = 0 ListBox1.Clear

     With Range("Forcast")

         Set textbox = .Find(TextBox1, LookIn:=xlValues, lookat:=xlPart)
         If Not textbox Is Nothing Then
             index = textbox.Address
             Do
                 result = Sheets("Report").Cells(Range(textbox.Address).Row, 1).Value
                 For Each item In ListBox1.List
                     If item = result Then match = 1
                 Next item
                 If match = 0 Then ListBox1.AddItem result
                 listbox1.ColumnCount = 4
                 Set textbox = .FindNext(textbox)
                 match = 0
             Loop While Not textbox Is Nothing And textbox.Address <> index
         End If End With

    End Sub

Upvotes: 0

Views: 1506

Answers (1)

paul bica
paul bica

Reputation: 10715

2 ways to add items to a ListBox:

Option Explicit

Private Sub UserForm_Initialize()

    With ListBox1


       .ColumnCount = 4 '---------------------------------------------

        'add a 2 dimensional array
        .List = Worksheets(1).Range("A1:D3").Value2 '3 rows, 4 columns

        'add a 2 dimensional array
        .List = Worksheets(1).Range("A1:D1").Value2 '1 rows, 4 columns



       .ColumnCount = 1 '---------------------------------------------

        'add a 1 dimensional array
        .List = Array(1, 2, 3, 4)                   '4 rows, 1 column

        'same as above:                             '4 rows, 1 column
        .AddItem "1"
        .AddItem "2"
        .AddItem "3"
        .AddItem "4"

    End With

End Sub

Upvotes: 1

Related Questions