I have created dropdown list which gives suggestions from existing data (like google search). I have followed the steps mentioned in the link to get the expected result. I have changed the process to VBA Code since the column range that has data can increase in time (Formula will be inserted for a range dynamically when a new entry is added). Now the problem is since the column have more than 20 000 rows it takes more time to display the suggestions when a key is pressed.
I need to get data from users in a form and store it in an excel sheet. Form will mostly contain dropdown search lists with search list having almost 100k values. Search list is maintained in a different excel sheet and the size of the list will increase every week. Is it ok to use Excel VBA to achieve this? If so how to improve excel performance?
I am using Excel 2010 in Windows Server 2008 OS. I can load the data to SQL Server Table. If this is not possible in Excel is there any way that I can use SQL Server to achieve this?
I don't know about performance, but give the following a try. Your data is supposed to be in sheet "db", from row 2 onwards. Insted of a combobox, I place a textbox (
TextBox1
) and a list (ListBox1
) in a UserForm.I can't solve what you pretend. I was able to fill a list according to input rather fast. However, code finds your input anywhere in the string, not in the beginning. Depending on your data, the following code might help you, or may be you can reformulate it to fit your needs. Again, data is supposed to be in sheet "db", from row 2 onwards, and there is a textbox (
TextBox1
) and a list (ListBox1
) in a UserForm.I did something similar to a google search in my excel app. My code waits 2 seconds after the last keystroke, then runs through another sheet to get close matches. This way it wasn't running through the DB whilst typing..
Wait until user has stopped typing in ComboBox to run macro (VBA)
Someone had suggested I actually look at this post but it was not helpful to my particular challenge.