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!

 

[Akuini]

Hi, Ekemas. Welcome to the Forum.

1. Maybe this searchable combobox suit your need, (please read post #2 & #15 in this thread):

Best way to create a searchable drop-down list with auto-complete functionality to cells in a column?

Hi Folks, I have a time-sheet workbook with two worksheets (ws). In sheet 1 (the time-sheet ws), Column A is "Employee". Time-sheet worksheet. Employee Start time Finish time Hours worked Joe Blogs Jane Doe I wish to be able to start typing a different employee on each row...

www.mrexcel.com

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:

How to filter whole row based on merged cells

Hello All, I've created an excel spreadsheet in which I've set buttons to filter rows based on values. The problem I'm having is that within the rows some cells are merged and when using the filter it won't keep the whole row. I'm uncertain of how to bring all the rows of over. The method I'm...

www.mrexcel.com

 

[Ekemas]

Hi, Ekemas. Welcome to the Forum.

1. Maybe this searchable combobox suit your need, (please read post #2 & #15 in this thread):

Best way to create a searchable drop-down list with auto-complete functionality to cells in a column?

Hi Folks, I have a time-sheet workbook with two worksheets (ws). In sheet 1 (the time-sheet ws), Column A is "Employee". Time-sheet worksheet. Employee Start time Finish time Hours worked Joe Blogs Jane Doe I wish to be able to start typing a different employee on each row...

www.mrexcel.com

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:

How to filter whole row based on merged cells

Hello All, I've created an excel spreadsheet in which I've set buttons to filter rows based on values. The problem I'm having is that within the rows some cells are merged and when using the filter it won't keep the whole row. I'm uncertain of how to bring all the rows of over. The method I'm...

www.mrexcel.com

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]

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:

https://accessanalytic.com.au/stop-merging-cells/

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]

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:

https://accessanalytic.com.au/stop-merging-cells/

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]

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:

Factuur_Baeten - Ekemas 1.xlsm

Shared with Dropbox

www.dropbox.com

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

 

[Ekemas]

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:

Factuur_Baeten - Ekemas 1.xlsm

Shared with Dropbox

www.dropbox.com

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]

You're welcome, glad to help & thanks for the feedback.

 

[Ekemas]

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):

The debugger points towards following part of the code:

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]

A member here had the same problem, see this discussion (please read post 21 & 26)

Count all values of each row from smaller to larger

Hello, I want to count from smaller to larger valve of each row and put them in one cell separated by vertical bar. Is it possible? As per example below Book1ABCDEFGHIJKLMNOPQRS12345n1n2n3n4n5n6n7n8n9n10n11n12n13n14Count Smaller to Larger67100010101100106 | 88211021002011006 | 5 |...

www.mrexcel.com

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:

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

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"