Autocomplete drop down lists

Ekemas

New Member
Joined
Dec 13, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Dec 13, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,960
Office Version
  1. 365
Platform
  1. Windows
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:


Note: if the combobox sometimes doesn't show up, try running this sub:
VBA Code:
Sub toEnable()
Application.EnableEvents = True
End Sub
 

Ekemas

New Member
Joined
Dec 13, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Feb 1, 2016
Messages
2,960
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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

New Member
Joined
Dec 13, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
Hello Akuini,

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

Best regards
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,960
Office Version
  1. 365
Platform
  1. Windows
Your welcome, glad it works.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,199
Messages
5,623,321
Members
415,966
Latest member
ctorohuamanchumo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top