Searching cells and populate listbox with results (userform)

[Wicked_]

Hi.
I've tried many different codes from different peoples, to make my search function to work, but with no hope, so i will be asking here.

So, what i want is this:

In A1, B1 and C1, i got different text (A1 = Tool ID: , B1 = Programs, C1 = Ordre)

and in the A column, i got different Tool ID's (example 500,501,502,503 etc etc)
and in the B column, i got program numbers
and in the C column, i got ordre numbers.

Then in the userform, i got a textbox and a listbox.
Within the textbox1, i want textbox1_change and when i type a number, it will show the result associated with the tool ID.

Example: If i got 100 Tool ID's and two of them got the tool id 500, and i search for 500, it will show these two results, with the tool id (500), and program number and ordre number.

I also use RowSource (name manager) with the code

=OFFSET('Maskin 51'!$A$2;0;0; COUNTA('Maskin 51'!$A:$A)-1;5)

, to try to get the "bar" in the listbox to show "tool ID, program and ordre.

Anyone that can help me with this search and show result code?

If my explanation sucks, please ask, and i will try to explain better.

Thanks

 

[Wicked_]

Sheet is called Maskin 51 yes.
=OFFSET('Maskin 51'!$A$2;0;0; COUNTA('Maskin 51'!$A:$A)-1;5) is located under "Formulas" (when looking at excel sheet), and Name Manager. Then i called it SearchResults

 

[My Aswer Is This]

So when you use RowSource you see all the values in the sheet Range correct?
But you only want to see the values where in your example 500 is in column A
Correct. So your RowSource will not work in your situation correct?

 

[Wicked_]

If i use something like this

Private Sub CommandButton1_Click()
Dim RowNum As Long
Dim SearchRow As Long

RowNum = 2
SearchRow = 2

Worksheets("Maskin 51").Activate

Do Until Cells(RowNum, 1).Value = ""

If InStr(1, Cells(RowNum, 2).Value, TextBox1.Value, vbTextCompare) > 0 Then
Worksheets("Maskin 51").Cells(SearchRow, 1).Value = Cells(RowNum, 1).Value
Worksheets("Maskin 51").Cells(SearchRow, 2).Value = Cells(RowNum, 2).Value
Worksheets("Maskin 51").Cells(SearchRow, 3).Value = Cells(RowNum, 3).Value
SearchRow = SearchRow + 1
End If
RowNum = RowNum + 1
Loop

If SearchRow = 2 Then
MsgBox "No products were found that match your search criteria."
Exit Sub
End If

ListBox1.RowSource = "SearchResults"
End Sub

Then it shows the RowSource names correcty, with the =OFFSET('Maskin 51'!$A$2;0;0; COUNTA('Maskin 51'!$A:$A)-1;5) in name manager.

But when i press the search button, it lists all what i want, with the "header" witnin the listbox1, tho if i try to search for a tool, i get the "no product were found" message, and if i press multiple time it may duplicate the values in my cells, so this is totaly broken, but it shows the RowSource that works

EDIT: "SearchResults" is the name of the OFFSET code witnin name manager

 

[My Aswer Is This]

Try this and see if this does what you want except for the Title Headers.
You will need a TextBox named TextBox1
Enter the search Value in the textbox then run the script by clicking on the button with this code.

Private Sub CommandButton2_Click()
'Modified 3/24/2020 7:08:06 PM EST
Dim i As Long
Dim b As Long
Dim c As Long
Dim ans As String
ans = TextBox1.Value
b = 0
ListBox1.Clear
ListBox1.ColumnCount = 3
ListBox1.ColumnWidths = "100;100;100"
Dim Del As Variant
Del = Array(1, 2, 3)
Sheets("Maskin 51").Activate
    For c = 0 To 2
    For i = 2 To Sheets("Maskin 51").Cells(Rows.Count, Del(c)).End(xlUp).Row
        With ListBox1
If Cells(i, 1).Value = ans Then
.AddItem
.List(b, c) = Sheets("Maskin 51").Cells(i, Del(c)).Value
b = b + 1
End If
End With

Next
b = 0

Next
If ListBox1.ListCount = 0 Then
MsgBox TextBox1.Value & " Not Found"
   End If
End Sub

 

[My Aswer Is This]

I made a way to have first row the way you showed. But will wait and see if the script I posted works for you.
RowSource not a option in your situation.
Your not wanting all the rows in the range just the ones you search for.

 

[Wicked_]

Cool, thanks

 

[My Aswer Is This]

Try this one:
It will put row 1 values in Listbox

Private Sub CommandButton2_Click()
'Modified 3/25/2020 10:11:39 AM EST
On Error GoTo M
Dim i As Long
Dim b As Long
Dim c As Long
Dim ans As String
Dim Del As Variant
If TextBox1.Value = "" Then MsgBox "You Must enter a search value in TextBox1" & vbNewLine & "I will stop the script": Exit Sub
With ListBox1
.ColumnCount = 3
.ColumnWidths = "25,25,25"
.Clear
.AddItem
.List(0, 0) = Cells(1, 1).Value
.List(0, 1) = Cells(1, 2).Value
.List(0, 2) = Cells(1, 3).Value
End With
ans = TextBox1.Value
b = 1
Del = Array(1, 2, 3)
Sheets("Maskin 51").Activate
    For c = 0 To 2
    For i = 2 To Sheets("Maskin 51").Cells(Rows.Count, Del(c)).End(xlUp).Row
        With ListBox1
If Cells(i, 1).Value = ans Then
.AddItem
.List(b, c) = Sheets("Maskin 51").Cells(i, Del(c)).Value
b = b + 1
End If
End With

Next
b = 1

Next
If ListBox1.ListCount < 2 Then
MsgBox TextBox1.Value & " Not Found"
End If
Exit Sub
M:
   MsgBox "We had some sort of problem"
End Sub

 

[Wicked_]

This worked perfectly! just the way i imagined it.
Thanks a ton mate for your time and help

 

[My Aswer Is This]

This worked perfectly! just the way i imagined it.
Thanks a ton mate for your time and help

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

 

[Wicked_]

I notice that after a while, it suddenly triggers the "we had some sort of problem" message, and cant search anymore because of it, if i restart the excel program and try again, it works, even if i save it first.
Strange.