Log In
Register
Home
May 11, 2019
Offline
Hi,
I have the attached project in which I used a combox that fills as you type. I used this option instead of the regular dropbox, because at time we have more than 1000 options to choose from, and it easier for the user to start typing and selecting.
The only issue I am having with the combox, is that it has tremendous lag, and at times, it seems to appear in other sheets on the same document.is there a way that this can be sped up with vba? Or is there maybe an option in the preferences that I need to change?
Any help would be of great assistance.
Trusted Members
December 20, 2019
Offline
Hi Robert
Combobox
I have not had any experience with the Combo box so not sure why it is appearing on other sheets - i would guess it is a resource issue but not sure.
Speed
There are a couple of things I think might help
Some of the speed issue I think might be because you have the best part of 12000 array formulas... I have updated your worksheet a bit so that you have the small array formula in column H to give you the row number {=IFERROR(SMALL(IF(paste!$E$2:$E$10050=$J$10,ROW(paste!$A$2:$A$10050)-1),ROWS(paste!$E$2:E2)),"")} then we do a straight index on the other 5 columns. I am not saying it’s perfect but it does clear out 10000 array formulas.
You might want to change the file type to an xlsb as (apparently) a binary workbook is more efficient than a .xlsm
Do you really need 2000 output rows on Sheet1 and 10000 lookup rows on the paste sheet? Even cutting these by half should give you a performance boost.
Maybe you could use advance filter to filter the records on to a temp worksheet and then link them to sheet1?
lastly you might want to have VBA populate the formulas in only the cells that need them rather than the potentially 11500 unused formulas that are sitting in the spready.
Let us know how you get on
Purfleet
1 Guest(s)
