Autocomplete drop down lists

Ekemas

New Member
Joined
Dec 13, 2020
Messages
8
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!
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,937
Office Version
  1. 365
Platform
  1. Windows
Hi, Ekemas. Welcome to the Forum.

1. Maybe this searchable combobox suit your need, (please read post #2 & #15 in this thread):
If you're interested in this method I think I can amend the code to meet your requirements.


2.
These cells B are merged with the ones from C
Merge cells? You should avoid that because it can cause many problems.
Check this discussion:
 

Ekemas

New Member
Joined
Dec 13, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Hi, Ekemas. Welcome to the Forum.

1. Maybe this searchable combobox suit your need, (please read post #2 & #15 in this thread):
If you're interested in this method I think I can amend the code to meet your requirements.


2.

Merge cells? You should avoid that because it can cause many problems.
Check this discussion:
Hi Akuini,

Thanks a lot for your fast reply! It is almost perfectly what I'm looking for.
I integrated the code such that cell C13 in sheet ("Factuur") uses the input from column A on sheet ("Klantengegevens"), which is working perfectly.

However still some things should be integrated of which I don't know how to do it:
  • In need to implement the same code but then for cells B21-B61" and Cells "B71-B138" (merged with C) in sheet ("Factuur"). But input should be taken from a different sheet ("Productenlijst")
  • Some nice to haves but not sure if possible:
    • Undo button stops working when integrating your code
    • Would it be possible to show the combobox on the cell which is to be filled and not on the right of it?
    • Would it be possible to jump below after enter and not to the right?
I uploaded my file to google drive, maybe if you see this it makes more sense what I'm trying to do: Factuur_Baeten.xlsm

Thanks a lot already for your help!

Kind regards
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,937
Office Version
  1. 365
Platform
  1. Windows
In need to implement the same code but then for cells B21-B61" and Cells "B71-B138" (merged with C) in sheet ("Factuur").
The combobox will not work with merged cells.
So I can't help you to set it up unless you can remove the merged cells.
Please read this:

Undo button stops working when integrating your code
When a macro changes/writes something in the sheet it will clear the Undo Stack, so at that point you can't use undo.
In this case whenever you put the cursor in the range with the combobox it will clear the Undo Stack.

Would it be possible to show the combobox on the cell which is to be filled and not on the right of it?
I can amend the code to do that

Would it be possible to jump below after enter and not to the right?
I can amend the code to do that
 

Ekemas

New Member
Joined
Dec 13, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

In need to implement the same code but then for cells B21-B61" and Cells "B71-B138" (merged with C) in sheet ("Factuur").
The combobox will not work with merged cells.
So I can't help you to set it up unless you can remove the merged cells.
Please read this:

Undo button stops working when integrating your code
When a macro changes/writes something in the sheet it will clear the Undo Stack, so at that point you can't use undo.
In this case whenever you put the cursor in the range with the combobox it will clear the Undo Stack.

Would it be possible to show the combobox on the cell which is to be filled and not on the right of it?
I can amend the code to do that

Would it be possible to jump below after enter and not to the right?
I can amend the code to do that
Hi Akuini,

Thanks again for your prompt feedback!

I removed the merged cells in my document.
So this is my request:
  • I already implemented the code for cell C13 which is working properly!
    But now I need to implement it on cells C21-B61 and cells C71-C138 (no longer merged cells :)) but I'm not sure how to that that since these comboboxes would need to take the input from a different sheet ("Productenlijst")
  • Some small adjustments if possible to the current working of the code:
    • Jump below after filling the cell
    • highlight combobox on top of cell to be filled and not on the right
    • Is it possible that next to pressing enter there is also a possibility to select the proper item in the drop down and then just click "somewhere else in the sheet" such that it also takes the proper item which was clicked (not sure if I'm explaining it properly), let me know if you don't understand what I'm trying to say.
You can find the excel sheet with my current code here: Factuur_Baeten.xlsm

Thanks already for your major support!
Looking forward to your reply!

Kind regards
Sam
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,937
Office Version
  1. 365
Platform
  1. Windows
I already implemented the code for cell C13 which is working properly!
But now I need to implement it on cells C21-B61 and cells C71-C138 (no longer merged cells :)) but I'm not sure how to that that since these comboboxes would need to take the input from a different sheet ("Productenlijst")

Done

Jump below after filling the cell
Done

highlight combobox on top of cell to be filled and not on the right
Done

Is it possible that next to pressing enter there is also a possibility to select the proper item in the drop down and then just click "somewhere else in the sheet" such that it also takes the proper item which was clicked (not sure if I'm explaining it properly), let me know if you don't understand what I'm trying to say.
Sorry, I don't know how to do that

The workbook:


I can see now you have merged cells in col A-B. Why not unmerged the cells, hide column B, & makes Column A wider?
 
Solution

Ekemas

New Member
Joined
Dec 13, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I already implemented the code for cell C13 which is working properly!
But now I need to implement it on cells C21-B61 and cells C71-C138 (no longer merged cells :)) but I'm not sure how to that that since these comboboxes would need to take the input from a different sheet ("Productenlijst")

Done

Jump below after filling the cell
Done

highlight combobox on top of cell to be filled and not on the right
Done

Is it possible that next to pressing enter there is also a possibility to select the proper item in the drop down and then just click "somewhere else in the sheet" such that it also takes the proper item which was clicked (not sure if I'm explaining it properly), let me know if you don't understand what I'm trying to say.
Sorry, I don't know how to do that

The workbook:


I can see now you have merged cells in col A-B. Why not unmerged the cells, hide column B, & makes Column A wider?
Hi Akuini,

You're a true hero man, don't think I could have done this on my own!
Don't know how to thank you!

With respect to the merged cells you're indeed correct :) I changed it to your suggestion (I'll try to avoid merged cells in the future.

Once more, big thank you for your help!

Kind regards
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,937
Office Version
  1. 365
Platform
  1. Windows
You're welcome, glad to help & thanks for the feedback.
 

Ekemas

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

I finally finished the full project on my laptop and it was working great!
Currently I want to run the same file on a different computer, but now there seems to be an issue with running the code you've written.

I get a runtime error when I try to fill the combobox as you can see below (it's in Dutch):
2020-12-31 15_52_42-Foto's.png


The debugger points towards following part of the code:

2020-12-31 15_53_31-Foto's.png


After browsing the web I see a lot of problems similar to this one pointing out not having NET frameworks 3.5 installed on the desktop.
However I'm struggling to install it on the computer where this excel needs to run.

Therefore I wanted to check with you if you are familiar with this error and if you know how I can avoid this?
The computer which has to use this excel has NET frameworks v4 installed (and thus not 3.5 I'm assuming).

Please let me know your thoughts on this.

Kind regards!
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,937
Office Version
  1. 365
Platform
  1. Windows
A member here had the same problem, see this discussion (please read post 21 & 26)

but the problem wasn't resolve so I wrote a different code.

But please try to solve the problem first, maybe you can resolve it.
And please test this on your computer:

VBA Code:
Sub try_1()
Dim dar As Object
Set dar = CreateObject("System.Collections.ArrayList")
End Sub

VBA Code:
Sub try_2()
Dim dx As Object
    Set dx = CreateObject("scripting.dictionary")
End Sub


which one is working?
if the second one works then I might amend the code using "scripting.dictionary"
 

Watch MrExcel Video

Forum statistics

Threads
1,122,940
Messages
5,598,975
Members
414,270
Latest member
skipolmsted

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