ListBox attached to a Search button

TekillaSunrize

New Member
Joined
Sep 24, 2021
Messages
12
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hello,

I have the following macro:

VBA Code:
Private Sub CommandButton1_Click()

Dim master_ticket_id As String
master_ticket_id = Trim(TextBox1.Text)
lastrow = ActiveWorkbook.Sheets(1).Cells(Rows.Count, 11).End(xlUp).Row

For i = 2 To lastrow

On Error Resume Next

If ActiveWorkbook.Sheets(1).Cells(i, 11).Value = master_ticket_id Then

ListBox1.ColumnCount = 5
ListBox1.ColumnHeads = True
ListBox1.ColumnWidths = "110;30;30;20"

ListBox1.AddItem

ListBox1.List(0, 0) = (ActiveWorkbook.Sheets(1).Cells(i, 8).Value)
ListBox1.List(0, 1) = (ActiveWorkbook.Sheets(1).Cells(i, 12).Value)
ListBox1.List(0, 2) = (ActiveWorkbook.Sheets(1).Cells(i, 10).Value)
ListBox1.List(0, 3) = (ActiveWorkbook.Sheets(1).Cells(i, 15).Value)


End If
Next

End Sub

The problem is that when i search for the number using textbox1 and i press the CommandButton1 it brings me only one result and not all the results associated with my search keyword, like in the example below:

asd.PNG


Appreciate if anyone could help me with this,
Thanks!
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Give this a try
VBA Code:
Private Sub CommandButton1_Click()

Dim master_ticket_id As String
Dim lastrow As Long

master_ticket_id = Trim(TextBox1.Text)
lastrow = ActiveWorkbook.Sheets(1).Cells(Rows.Count, 11).End(xlUp).Row

    ListBox1.ColumnCount = 5
    ListBox1.ColumnHeads = True
    ListBox1.ColumnWidths = "110;30;30;20"

For i = 2 To lastrow
    If ActiveWorkbook.Sheets(1).Cells(i, 11).Value = master_ticket_id Then
        With ListBox1
            .AddItem ActiveWorkbook.Sheets(1).Cells(i, 8).Value
            .ListIndex(.ListCount - 1, 1) = ActiveWorkbook.Sheets(1).Cells(i, 12).Value
            .ListIndex(.ListCount - 1, 2) = ActiveWorkbook.Sheets(1).Cells(i, 10).Value
            .ListIndex(.ListCount - 1, 3) = ActiveWorkbook.Sheets(1).Cells(i, 15).Value
        End With
    End If
Next i
            
End Sub
 
Upvote 0
Thanks for the replay!

It stops after this line :( :

With ListBox1
.AddItem ActiveWorkbook.Sheets(1).Cells(i, 8).Value

error.PNG


Error type: mismatch
 
Upvote 0
what value is in ActiveWorkbook.Sheets(1).Cells(i, 12) when this occurs ?
 
Upvote 0
Sorry, my bad... it should be .List not .ListIndex
i should also be declared
VBA Code:
Private Sub CommandButton1_Click()

Dim master_ticket_id As String
Dim lastrow As Long, i As Long

master_ticket_id = Trim(TextBox1.Text)
lastrow = ActiveWorkbook.Sheets(1).Cells(Rows.Count, 11).End(xlUp).Row

    ListBox1.ColumnCount = 5
    'ListBox1.ColumnHeads = True
    ListBox1.ColumnWidths = "110;30;30;20"

For i = 2 To lastrow
    If ActiveWorkbook.Sheets(1).Cells(i, 11).Value = master_ticket_id Then
        With ListBox1
            .AddItem ActiveWorkbook.Sheets(1).Cells(i, 8).Value
            .List(.ListCount - 1, 1) = ActiveWorkbook.Sheets(1).Cells(i, 12).Value
            .List(.ListCount - 1, 2) = ActiveWorkbook.Sheets(1).Cells(i, 10).Value
            .List(.ListCount - 1, 3) = ActiveWorkbook.Sheets(1).Cells(i, 15).Value
        End With
    End If
Next i
            
End Sub
 
Upvote 0
Solution

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top