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
Display More