VBA Combobox 'smart search' with 2 columns

[guitard]

Good afternoon,

I apologize if this has been asked/answered already. I have done extensive searches and spent many hours trying to debug with no luck. I will try and be as clear and concise with what I am trying to accomplish and hopefully someone can assist. I am slowly learning VBA within excel and attempting to solve a dropdown issue to allow searching.

Objective: Provide a combobox with 2 columns; return column1 value while allowing the user to conduct a smart search (e.g., filter the list to just items containing what the user inputs) within the description of column2 to make the selection easier.


Workbook details:
I have a sheet called 'Database' containing a table 'Table1' with 8 columns, currently unnamed headers. The only relevant columns are 'Column1' is numbers (lets say 1 through 10) and 'Column2' is a description of each item in column1 (product descriptions). I have a userform called 'Tool' (with a few settings: BoundColumn 1, ColumnCount 2) and a combobox on the form called 'newCmb'.


So far I have pieced together code from several locations to accomplish a few things. It initializes the combobox from Table1 and allows me to search the list with the mousewheel, up/down arrows and pageup/down buttons without selecting the items, and a mouseclick will select the item and return the column1 value.

Where I am stuck:
The borrowed code does conduct the smart search, however the filtered list is just the column2 data. I do not know how to get the filtered list to display both columns as how it initializes (column1 column2).

Another glitch, if you press 'enter' on the keyboard after highlighting a selection will bring an empty list, which I assume is related to the filtering function only looking at column2, .

Option Explicit

Private cLst As Variant

Private Sub UserForm_Initialize()
    With newCmb
        .List = Range("Table1").Value
    End With
End Sub


Private Sub newCmb_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

Const vbPageUp = 33
Const vbPageDn = 34
   
If KeyCode <> vbKeyUp And KeyCode <> vbKeyDown And KeyCode <> vbPageUp And KeyCode <> vbPageDn Then
    filterComboList Tool.newCmb, cLst 'Run the filtering function
    Tool.newCmb.DropDown
End If

End Sub

Private Sub newCmb_GotFocus()
    Tool.newCmb.DropDown
End Sub

Public Sub filterComboList(ByRef cmb As ComboBox, ByRef dLst As Variant) 'Filtering function
    Dim itm As Variant, itmcode As Variant, lst As String, sel As String, rng As Range
    With ThisWorkbook.Sheets("Database")
    Set rng = Application.Intersect(.UsedRange.Columns(2), .Cells.Resize(.Rows.Count - 2).Offset(2)) 'Exclude first two cells in column A
    End With
    
    Application.EnableEvents = False
    
    With cmb
        sel = .Value
        If IsEmpty(cLst) Then cLst = rng
        For Each itm In cLst
            itmcode = itm
            If Len(itm) > 1 Then
                If InStr(1, itm, sel, 1) Then
                    lst = lst & itm & "||"
            End If
        End If
        Next
        If Len(lst) > 1 Then
            .List = Split(Left(lst, Len(lst) - 1), "||")
        Else
            .List = Range("Table2").Value
        End If
    End With
    Application.EnableEvents = True
   
End Sub

Private Sub newCmb_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal Y As Single)
    HookListBoxScroll Me, Me.newCmb
End Sub

Private Sub Tool_QueryClose(Cancel As Integer, CloseMode As Integer)
    UnhookListBoxScroll
End Sub

If someone knows of a solution to displaying multiple columns in the filtered list you will be a lifesaver!

Thank you.

 

[guitard]

I also forgot to mention,

I have referenced 'Table2' within the filtered list of items. It is a 2 column table with 1 row of nothing.

I was unsure of how to accomplish displaying a list of nothing if the user inputs values to search that have no results.