I am trying to create a self filtering list in a combobox that sorts itself as the user types. Thanks to my other thread in the forum, I have created an autofilter on the main list in column A (the range is called "TransferringAccountIdenti
fier") . The filter matches the combobox1.value and then autofilters and copies the shortened list as a range to cell G1.
The combo1.list is filled from the "G1" range - ComboBox1.List = Sheet3.Range("G1").Current
I have also tried a similar method by using rowsource and mapping it to ComboBox1.List = Sheet3.Range("G1").Current
ress. This also works fine.
The first major problem is that as the list gets down to one last result only (by either typing enough characters or by clicking on one of the list), it churns out an error.
It seems well documented that .list cannot cope with one item. This is a known problem with combo boxes and the same for rowsource. It has to be an array of 2 or more. So when we get down to one value, we have an error using .list and .rowsource. That means we have to resort to deleting the list and using .additem. (unless anyone can suggest an alternative)
So, no problem. I have trapped the - Sheet3.Range("G1").Current
nt = 1 and if it is only one item, I then attempt to clear the list and just load the single item.
I cannot get the list to clear. How do I unbind the list or rowsource so that the list is cleared???
I have tried combobox1.clear - see the command button
I have tried setting the combobox1.list =""
I have tried setting the combobox1.rowsource =""
I simply cannot get rid of the list.
The second problem is if I enter enough characters (try 'glu'), I get a match and down to a single entry in the filter list. The trap picks up the single entry and moves to the click event but I am unable to force the full vale into the input part of the combobox.
I could probably do it using .additem throughout but as the full list has 90,000 in it, I would rather use list or rowsource.