Function FindStringinListControl and Filter Search by first letter entry

[Excelnoub]

Good day,
I am looking to add some code to the following function provided, somewhere online don't quite remember, but the main idea is there.

The following function will make my comboboxes to search for the Letter that I will manually insert in my combobox.

I need to have the following also but I cannot find a method anywhere. I need that, if I insert, let say , letter A in my combobox, to search my list and filter everything starting with the letter a to be viewed in my combobox. If nothing is inserted in the combobox, then, show everything.

Example of my current code in UserForm:

Private Sub UserForm_Initialize()
Dim LastRow As Long
LastRow = Worksheets("Control").Range("V2").End(xlDown).row
Me.ComboBox1.List = Worksheets("Control").Range("V2:V" & LastRow).Value
End Sub

In a Module:

Private Declare Function SendMessagebyString Lib _
"user32" Alias "SendMessageA" (ByVal hWND As Long, _
ByVal wMsg As Long, ByVal wParam As Long, _
ByVal lParam As String) As Long
Private Const LB_FINDSTRINGEXACT = &H1A2
Private Const CB_FINDSTRINGEXACT = &H158
Public Function FindStringinListControl(ListControl As Object, ByVal SearchText As String) As Long
'Input:
'ListControl: List or ComboBox Object
'SearchText: String to Search For
'Returns: ListIndex of Item if found
'or -1 if not found
 
Dim lHwnd As Long
Dim lMsg As Long
On Error Resume Next
lHwnd = ListControl.hWND
If TypeOf ListControl Is ListBox Then
  lMsg = LB_FINDSTRINGEXACT
ElseIf TypeOf ListControl Is ComboBox Then
  lMsg = CB_FINDSTRINGEXACT
Else
  FindStringinListControl = -1
  Exit Function
End If
FindStringinListControl = SendMessagebyString(lHwnd, lMsg, -1, SearchText)
End Function

Now this function will add the name of what you are searching for in the textbox each time you add a letter in the combobox.

I am looking to implement, which I don’t have, a method that if I insert a letter, let’s say “A”, to show in the combobox only the value of text that starts with the letter A.

Another Example is if my list has Apple, Banana, Brian and Jim, then if I add B in my Combobox then Banana will populate automatically but if the drop down selection is selected then the list will only have Banana and Brian.

This list will always change and I have some weird names.

Is this possible to use the above function to implement what is requested?

 

[Luke M]

Here's an example using a ComboBox. You can use it to learn the concept, and apply the same to a UserForm. Uses only a named range.

 

[Excelnoub]

Yeah this could get tricky...

Your method works but just wondering if this could be implemented within my Module Function...
Problem would be this:

I will have a list of 100 and more entries and each day/week/month more entries will be added, therefore this is why I cannot go with a name range and plus I have other function that pulls out information from another workbook.
I need that All my combobox for all workbooks that will be copied with the original wb have the same standard functions.

I would need that every time you add a letter to the combobox to search for everything with that letter but also something I omitted to say. I apologize.

Is there a way to, let’s say I have a name John Doe... So if the user inserts Doe within the combobox it will search everything with Doe therefore in the list in my combobox will become John Doe... but if I have John Doe and John Right, then the combobox will identify both John’s as selection. If the user adds the letter “J” within the combobox then it will populate all entries starting with the letter J.

 

[Luke M]

Yes, it will get tricky. Since you're dealing with VB, you should already have some way of defining your range that the list is coming from, so that you can populate your UserForm. You don't need to use named Ranges, you could write those directly into the code if you want. My example was just a way of showing how it could be done.

For the latter, that gets trickier. Now instead of having a sorted list and just picking a single block, you'd have to search through your list and pull out the correct entires. I suppose you could have the UserForm load all possible entries into a Dictionary object, and then at every change search through the dictionary and compare for matches, build a new list, and feed that List to combo box. The problem then is that I'm not sure the code could run fast enough to keepup with a decent typing speed.

 

[Excelnoub]

Thank you Luke... I was just wondering... The method in my WB (in this thread) works I am just not that far in to VBA coding to implement these kind of codes lol

 

[Excelnoub]

Luke M,
Just to let you know that I found something similar to what I needed. I will try to modify this to a Combobox