Akuini

Macro to create searchable data validation+combobox

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

Attachments

  • First time.jpg
    First time.jpg
    5.6 KB · Views: 29
  • Second time.jpg
    Second time.jpg
    3.1 KB · Views: 28

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
entryexitexcel said:
my excel is 85% zoomed, and the list looks so small the first time, smaller the second time, and goes on.
Click to expand...
I can't reproduce such behavior.
The size of the combobox is set in "Sub toShowCombobox()", in this part
VBA Code: 
            .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:
VBA Code: 
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.
 
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".
 
RajK2005 said:
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".
Click to expand...
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?
 
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.
 
Excellent Sir its very Simple and Useful Add-in .

Thanks of lots
 
You must log in or register to reply here.

Forum statistics

Threads
1,216,503
Messages
6,131,022
Members
449,616
Latest member
PsychoCube

Share this page

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
Back
Top