Search function within Form Control ComboBox

Hi all,

Is there a way to add a search function to a Form Control ComboBox by using a VBA code? As it seems, such function does not originally exist within Excel. Basically, As illustrated in the image below, I need to search for a particular store (1500+ stores available).

E.g. If I start typing "Ocean Terminal Hon" it automatically suggest all stores which consist of "Ocean Terminal Hon" such as store "Ocean Terminal Hong Kong".

Is that even feasible within Excel?

Hi esbencito,
please attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Hi nilem,

See file attached!

maybe so

Private Sub ComboBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim x, txt$, i&, s$

x = Sheets("Location Plan").Range("Store_Name").Value
txt = Me.ComboBox1.Value

For i = 1 To UBound(x)
    If InStr(x(i, 1), txt) Then s = s & "~" & x(i, 1)
Next
Me.ComboBox1.List = Split(Mid(s, 2), "~")
Me.ComboBox1.DropDown

End Sub

That works almost as expected! Thanks!!

The only call out I would have is that it doesn't neglect UPPER and LOWER case letters. If I type lower case it does not find all stores as some are in capital letters. Is is possible to adjust the code to show all stores nevertheless neglecting letter capitalization?

Also, how could I have a second combobox with the store IDs? e.g if I only know the store ID but not the name, I want to simply search for the ID number in a store ID combobox and the other combobox matches the name accordingly. Is that feasible somehow?

try it (see attachment)

The letter capitalization problem is resolved now! Thanks!!

Regarding the Store ID search combobox, I cannot actually "search" in it, as it only displays all (!) store ID values. Also, whenever I search for a store name, I doesn't vlookup the corresponding store ID only the other way around. Is there a way to have both (!) comboboxes with a search function and then the one that isn't used as a search, just vlooksup the other boxes value? Or would the codes interfere with each other?

Here is all the code (in the 'Store Tracker' sheet module)
You can select the ID, and you will see the store's name in the second combo. Or you can choose the name of the store and you will see its ID in the first combo.

Option Explicit
Option Compare Text

Private Sub ComboBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim x, txt$, i&, s$

x = Sheets("Location Plan").Range("Store_Name").Value
txt = Me.ComboBox1.Value

For i = 1 To UBound(x)
    If InStr(x(i, 1), txt) Then s = s & "~" & x(i, 1)
Next
Me.ComboBox1.List = Split(Mid(s, 2), "~")
Me.ComboBox1.DropDown
End Sub

Private Sub ComboBox2_Change()
Dim s$
On Error Resume Next
s = WorksheetFunction.VLookup(Val(ComboBox2.Value), Sheets("Location Plan").Range("A5:B1500"), 2, 0)
Me.ComboBox1.Value = s
End Sub

Private Sub ComboBox1_Change()
On Error Resume Next
With WorksheetFunction
    Me.ComboBox2.Value = .Index(Sheets("Location Plan").Range("A5:A1500"), _
                                .Match(ComboBox1.Value, Sheets("Location Plan").Range("B5:B1500"), 0))
End With
End Sub

Thanks!! resolved the issue of not doing a vlookup whenever I search for a store name!

Though, I still cannot 'search' within the store ID combobox as I can within the store name combobox.

Is is feasible to have below function for the store ID combobox as well? So it would basically vlookup the corresponding store ID if I search within the store name combobox and it would vlookup the name if I do a search within the store ID combobox. So depending on which combobox I do a search in, it vlooksup the other value...

Private Sub ComboBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim x, txt$, i&, s$

x = Sheets("Location Plan").Range("Store_Name").Value
txt = Me.ComboBox1.Value

For i = 1 To UBound(x)
    If InStr(x(i, 1), txt) Then s = s & "~" & x(i, 1)
Next
Me.ComboBox1.List = Split(Mid(s, 2), "~")
Me.ComboBox1.DropDown
End Sub

I do not think that it really is necessary
but try

Private Sub ComboBox2_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim x, txt$, i&, s$

x = Sheets("Location Plan").Range("Store_ID").Value
txt = Me.ComboBox2.Value

For i = 1 To UBound(x)
    If InStr(x(i, 1), txt) Then s = s & "~" & x(i, 1)
Next
Me.ComboBox2.List = Split(Mid(s, 2), "~")
Me.ComboBox2.DropDown
End Sub

First, set the ListFillRange property to empty for Combo2

awesome!! nearly perfect! Thank you!!

Maybe this is too much to ask for, but now since I can search in both comboboxes, it would be incredible to have the other combobox's search affected too.

e.g

I type store ID "701" in the store ID combobox and it suggest me all store IDs that consist of "701" and then I go to the store name combobox and it suggest me all store names that have a store ID consisting of "701". Do you know what I mean?

( client request, so can't really decide what's necessary and what's not )

In this case, you need one combobox, which will display both the ID and the names.
Something like "3003 ~ Name01"

I see. At least I know now that what I’m trying to do isn’t feasible! Should be fine the way it is now. Thanks for all your help,highly appreciated!

I just realised that the search function does not work if the corresponding store ID has letters in it:

e.g.

Store ID: EVNT1 Store Name: EVENT JAPAN

Is there any way to fix this? Or do all IDs always have to be solely numbers?