I'm attempting to build a searchable drop down menu without using any helper columns. I have tried going about this myself, but cannot find a way to do this without using a helper column.
I have a workbook with two sheets. The dropdown is on Sheet 1, and it is propagated with data from a column in Sheet 2. For reasons I won't go into, I cannot make any changes to Sheet 2. I currently have a Worksheet_SelectionChange function that autocompletes the dropdown when the user types in it. I was hoping to cleanly swap out this function with one that presents the user with search results instead. I'm still a novice with VBA. I understand if nobody wants to write the function, but if you could point me in the right direction, I'd really appreciate it. Really, any help at all would be great.
FWIW, the autocomplete function I am using is below:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim xCombox As OLEObject Dim xStr As String Dim xWs As Worksheet Set xWs = Application.ActiveSheet On Error Resume Next Set xCombox = xWs.OLEObjects("TempCombo") With xCombox .ListFillRange = "" .LinkedCell = "" .Visible = False End With If Target.Validation.Type = 3 Then Target.Validation.InCellDropdown = False Cancel = True xStr = Target.Validation.Formula1 xStr = Right(xStr, Len(xStr) - 1) If xStr = "" Then Exit Sub With xCombox .Visible = True .Left = Target.Left .Top = Target.Top .Width = Target.Width + 5 .Height = Target.Height + 5 .ListFillRange = xStr .LinkedCell = Target.Address End With xCombox.Activate Me.TempCombo.DropDown End If End Sub