Autocomplete drop down lists

[Ekemas]

Hello All,

I'm struggling with a problem to autocomplete a cell based on a drop down list. I'm already using combobox but I would like to have following additional functionalities:

• When typing show all possible matches on the screen and remove options which are not possible
• Do this no matter where the match is found in the word, such that it's not necessary to start typing with the first character.
  Example: typing "ar" will show "argument" as well as "car"

The construction of my excel is as follow:

• Sheet 1 cell "C13" contains a dropdownlist generated from sheet2 Column A
• Sheet 1 cells "B21-B61" and Cells "B71-B138" contain a dropdownlist generated from sheet 3 Column A --> These cells B are merged with the ones from C (don't think this will make a difference in coding).

I found some code online but it just autocompletes when I start correct with the first character of each word and also it will always display the complete list and not only the matches.
Hoping someone can help me out!

 

[Ekemas]

Hello Akuini,

Thanks for your feedback.

I've read the posts on the topic you referred to.
I can find the mscorlib.tbl file in my explorer, but when I enter it in my code a get an error: Run-time error '32813' : 'Name conflicts with existing module, project, or object library'
So far I'm not able to get it working on my own.

However with respect to the two parts of code which you mentioned above:
The first sub 'try_1()' is not working.
The second sub 'try_2()' is indeed working in my excel file (no error while running).

File can be found here: Factuur_Baeten.xlsm

Looking forward to your feedback!

Thanks in advance.

 

[Akuini]

Ok, I've changed the code to use "scripting.dictionary" instead of "System.Collections.ArrayList".

But you'll need a helper column (to sort data). I set col F in sheet "Productenlijst" as helper column. You may change that in this line (in "Sub ComboBox1_GotFocus()":

'sort data using helper column
Set hp = Sheets(sList_2).Range("F1") 'change to suit

The workbook:

Factuur_Baeten - Ekemas - searchable combobox 4

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

Note: if the combobox sometimes doesn't show up, try running this sub:

Sub toEnable()
Application.EnableEvents = True
End Sub

 

[Ekemas]

Hello Akuini,

Thanks again for your great feedback, this code is working without any issues!
I have one small question however.

I wrote a functionality that based on the selected customer (combobox in cell C13) for some customers an automatic list will products will be filled in that sheet.
That code is written in module 7 of my excel sheet.
Normally the macro will be triggered when value in Cell C13 is changed.
However currently after changing the value in C13 I need to click a random combobox in the sheet before the values are actually updated.
I don't know if this has anything to do with the new code you generated?

Previously it was working perfectly (but that was of course at my other laptop)

Kind regards

 

[Akuini]

I don't know if this has anything to do with the new code you generated?

I think yes.
It has something to do with "Application.EnableEvents = False" & "Application.EnableEvents = True" in "Private Sub ComboBox1_KeyDown"
Replace the Sub with this one then see what happen:

Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        Select Case KeyCode
        Case 13 'Enter
           
           'Enter Key to fill the cell with combobox value
'            If IsError(Application.Match(ComboBox1.Value, Sheets(sList).Columns(sCol), 0)) Then
            If IsError(Application.Match(ComboBox1.Value, vList, 0)) Then
                If Len(ComboBox1.Value) = 0 Then
'                    Application.EnableEvents = False
                    ActiveCell = ""
'                    Application.EnableEvents = True
                    Else
                    MsgBox "Wrong input", vbCritical
                End If
            Else
'                Application.EnableEvents = False
                ActiveCell = ComboBox1.Value
                ComboBox1.Visible = False
'                Application.EnableEvents = True
                
                ActiveCell.Offset(ofs1, ofs2).Activate
            End If
        Case 27, 9 'esc 'tab
                ComboBox1.Clear
'                ActiveCell.Offset(ofs1, ofs2).Activate
                ActiveCell.Offset(, 1).Activate
        Case Else
            'do nothing
    End Select

End Sub

 

[Ekemas]

Hello Akuini,

That's working perfectly!
Thanks again for your major support!

Best regards

 

[Akuini]

Your welcome, glad it works.