That worked perfectly! This is really really cool. Seriously, thank you so much.
It worked this time! Very very cool.
I did test this with both the test data and my real data. There is one change I want to try to make. I'll try to explain this in a clear way.
Let's say I have a list of names in the column we're pulling data from. Something like:
In its current form, if I type "Johnson" in the combobox, it will return 0 results. If I type "Matt" it will return two results. Ideally, typing "Johnson" should return every item that contains "Johnson" as part of the name, just as it would when I type "Matt". How would you adjust the code to accomplish that?
Hi Carim. No luck still, however I found that this section of code
Is causing whatever cell is selected to be given the value in the combo box. I encountered a similar issue when first putting this together and came up with a workaround.
However, I cannot figure out why the dropdown from the combobox is still not filtering the results. I can tell it's working because the other values change while I type. The dropdown is the only thing that doesn't seem to want to update. I'm still in a little over my head, but I'm learning a lot. I had no clue about collections or dictionaries.
Hey Carim! Thank you very much. When I begin to type, I am not seeing a list of search suggestions in the dropdown. However, I am seeing the corresponding values in the neighboring cells change as I type, which is pretty cool. Another strange thing I noticed is that the value in the dropdown is copied to any cell I click on after changing the value. I'm going to sit down and play with the code to see if I can understand it. Let me know if you have any suggestions on what I can do. Again, thank you very much for your help!
I would like to be able to search for the data that is propagated in the dropdown that appears in cells C9 and D9.
I would like to emulate the behavior seen in tutorials like this one:
However, I need to do it without the use of helper columns.
Selecting data in the dropdown will pull relevant information from the list the appears on the second sheet. Clicking the button will add the selected data to the list. At this time the entire spreadsheet functions as intended. However, the autocomplete function is less useful in practice than I had hoped. A search function would be far more useful.
Absolutely. The file is attached to this post. Let me know if there are any questions.
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:Code
- 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
- End If
- End Sub