Macro to create searchable data validation+combobox

[entryexitexcel]

This is probably weird and may be I am the only one facing it
my excel is 85% zoomed, and the list looks so small the first time, smaller the second time, and goes on. What am I doing wrong here? Thanks!

I want the list to be visible clearly so user can choose from. My users are 50+ years

 

[Akuini]

my excel is 85% zoomed, and the list looks so small the first time, smaller the second time, and goes on.

I can't reproduce such behavior.
The size of the combobox is set in "Sub toShowCombobox()", in this part

            .Height = Target.Height + 5
            .Width = Target.Width + 10

as you can see the size is relative to the active cell size. Try experimenting with absolute size, something like this:

Sub toShowCombobox()

Dim Target As Range

Set Target = ActiveCell

        With ComboBox1
'            .Height = Target.Height + 5
            .Height = 25
            
'            .Width = Target.Width + 10
            .Width = 300
            
            .Font.Size = 14
            
            .Top = Target.Top - 2
            .Left = Target.Offset(0, 1).Left
            .Visible = True
            .Activate
        End With

End Sub

note: I added .Font.Size = 14 ,so you can also set the font size to suit.

 

[RajK2005]

Hi

I have been trying to implement this macro in my sheet for our client who may or may not be okay with Add-ins. Everything is fine and works great except one issue:
Whenever there are dropdowns with a large number of options, it gets slow and takes time to load. Can anything be done to fix this?

I have a column with around 18000 options and it takes almost a second which is fine but I have some columns which are 50K, 80K, 130K, 160K and they take 5-6 seconds.
I remember you mentioned in the add-in post about when userform is activated the listbox will be populated with the list in the 1st version but in the next version, you chose to populate it only after user either enters an input or presses the down arrow. How can I apply that method to this macro? I am using the "VERSION B - ACTIVATED AUTOMATICALLY".

 

[Akuini]

I have a column with around 18000 options and it takes almost a second which is fine but I have some columns which are 50K, 80K, 130K, 160K and they take 5-6 seconds.
I remember you mentioned in the add-in post about when userform is activated the listbox will be populated with the list in the 1st version but in the next version, you chose to populate it only after user either enters an input or presses the down arrow. How can I apply that method to this macro? I am using the "VERSION B - ACTIVATED AUTOMATICALLY".

1. Do you need to sort the list in the combobox?
If not, then I can amend the code to skip the sort part, because on large list the sort past could slow the process significantly.
Note: you don't need to sort the list if the list is already sorted in the sheet.
If you need to sort the list, then I can amend the code to use the new SORT function in Excel 365 to speed up the code, it means that it works only on Excel 365 or later.
2. I could also speed up the code by starting the search after you type the second character in the combobox? Is that ok?

 

[RajK2005]

Hi,
I was on leave for some time, so couldn't respond.

1) I don't need to sort the list. It is already sorted (using your code only). So pls let me know how to amend that part.
2) Pls share this as well. I would like to implement both or either by testing both and choosing which once best fits our clients convenience.

Also, some times, few are getting an Automation error with debug option. Do you know how to fix this? See screen recording here. It doesn't happen in my system but 2 clients have mentioned this error. Same error and same code. I also found a solution for this on Stackoverflow but since that involves installing .NET 3.5, something which I cannot suggest our clients, do you have any alternatives?

I have told them turn off the searchable toggle and use the dropdowns usual way for now but if any solution is possible, let me know pls.

 

[Mohamedazees]

Excellent Sir its very Simple and Useful Add-in .

Thanks of lots

 

[RajK2005]

Any update on my requests Akuini?

 

[Akuini]

Any update on my requests Akuini?

I'm sorry, I'm currently busy. I should have some time next week to work on the code you requested.

 

[Akuini]

Excellent Sir its very Simple and Useful Add-in .

Thanks of lots

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

 

[RajK2005]

I'm sorry, I'm currently busy. I should have some time next week to work on the code you requested.

Cool. Thank you.