Set VBA search function to only search one row and also not show duplicates?

Any ideas or info is awesome!

Fill your sample file with sample data.

i also want the box to auto update as new entrys are put into the cutlist.

You'll have to explain how the cutlist is updated. From the userform? If so, then call the paintbox1_change sub from wherever the update happens.

Just with some quick fill data, the listbox appears to work as listed.

To have a unique list, use a collection, because collections can't have duplicate data (well, keys actually). We take advantage of that by attempting to put all the data in the collection. The collection refuses to insert the duplicate data and because we have error reporting turned off, we can pass by without stopping. So we end up with a unique list.

Private Sub PaintBox_Change()
    Dim rng As Range, e
    Dim UNIQUE As New Collection
    
    With Me
        .PaintListBox1.Clear
        If Len(.PaintBox.Value) Then
            For Each e In Sheets("CutList").Cells(1).CurrentRegion.Columns(1).Offset(1).Value
                If (e <> "") * (e Like "*" & .PaintBox.Value & "*") Then
                    On Error Resume Next
                    UNIQUE.Add CStr(e), CStr(e)
                    On Error GoTo 0
                End If
            Next
           'Iterate the collection and stuff the listbox.
            For e = 1 To UNIQUE.Count
                .PaintListBox1.AddItem Unique(e)
            Next
            
            With .PaintListBox1
                If .ListCount > 0 Then .ListIndex = 0
            End With
        End If
    End With
End Sub

This works exactly as I would like it to. Only thing I need to do yet is get it to search a column based on a variable.

So basically I want to set a variable say: "searchnum" and based on the value of search num I want this to select a different column to pull values from.

So if I set the value of "searchnum" to 3 I want it to search column d. and etc.

I have tried some stuff but for whatever reason I cant even get it to search a specific column other than the first one, much less get it to pull those column values from a variable.

I reattached the cutting list with the code that Tinbendr came up with (THANKYOU BTW!!!!)and some demo data so that everyone can see what types of data that get thrown into the sheet.


NEW CUTTING LIST DEMO.xlsm

[EDIT]

I got the search by column working but I cant figure out how to set it to a variable... This is waht I have:

Private Sub PaintBox_Change()
    Dim rng As Range, e
    Dim UNIQUE As New Collection
    Dim srchnum As String
    
    srchnum = "D"
    
    With Me
        .PaintListBox1.Clear
        If Len(.PaintBox.Value) Then
            For Each e In Sheets("CutList").Cells(1).CurrentRegion.Columns(srchnum.Value).Offset(1).Value
                If (e <> "") * (e Like "*" & .PaintBox.Value & "*") Then
                    On Error Resume Next
                    UNIQUE.Add CStr(e), CStr(e)
                    On Error GoTo 0
                End If
            Next
           'Iterate the collection and stuff the listbox.
            For e = 1 To UNIQUE.Count
                .PaintListBox1.AddItem UNIQUE(e)
            Next
            
            With .PaintListBox1
                If .ListCount > 0 Then .ListIndex = 0
            End With
        End If
    End With
End Sub

I'm still confused as how you intend to select the column?

OK, I understand now.

This will take a lot of coding to accomplish, but the premise is:
1. Remove the Paintbox control.
2. Whichever control the user is typing it will determine which column to search.
a. Create a subroutine to fill the listbox. In each control change event, call the subroutine, passing the range (column) information.
b. We'll have to store the active control name somewhere (probably the listbox tag property), so when the listbox item is double-clicked, the code will know the correct control to sent it to.

Sound right?

OK, try this.

The routine to fill the paintbox has been moved to the UpdateForm module.

Each control that you want to search from has to have change event code call the ftnPaintBox.

Look at the Detail Drawing and the Item Number textbox change events in the userform.

I've left the drudgery of changing all the events to you.

What is supposed to happen when you double click?

The objects in the dblclick event do not exist. But I can't remember how it's supposed to work.