J
James Buist
I have gone around the hoop with testing the combo box - ActiveX control in
Excel 2003.
What I’m trying to do is to set a combobox with a 2 column list from a
worksheet and have the auto complete actually work! There are a number of
problems as follows:
Prob 1 - If you just retrieve a list from a worksheet, it cannot contain
blank rows otherwise they will be displayed in the combobox. Thus, ust
setting the listfillrange to a range in a workbook is no good here – There
will always be at least one blank row maybe more.
Solution 1 – I have used an array formula to create a list without spaces
and use an offset function to pick up the list. This work fine. I have even
defined the offset function as a name so I can easily reference it. I’m using
this for data validation.
Problem 2 – data Validation with dropdown doesn’t’ provide any matching
function – its just a drop down list. Thus I have set up a way to
automatically display a combobox in the cells with data validation and have
set the listfill, size and other properties on activation. Again this works
fine, except, by referencing a range of formulae instead of just raw data,
the list matching stops working. The list displays but there is no
autocomplete. ON further research, autocomplete fails if you reference a
range of formulae rather than raw data. Or perhaps I should say complex
formula . Actually, if you reference the complex formula range to another
range on a different sheet using a simple “=cellref†then it works again
however that is very impractical.
Solution 2 – Load the data into an array and then set the list from the array.
Problem 3 – that doesn’t work either. The autocomplete fails when you load
from an array.
I’ve got to the end of my self discovery and am wondering if anyone can shed
some light or offer alternative suggestions. Its so frustrating as it would
make such a great solution and I feel that it should work and am so close but
can’t quite get there.
Excel 2003.
What I’m trying to do is to set a combobox with a 2 column list from a
worksheet and have the auto complete actually work! There are a number of
problems as follows:
Prob 1 - If you just retrieve a list from a worksheet, it cannot contain
blank rows otherwise they will be displayed in the combobox. Thus, ust
setting the listfillrange to a range in a workbook is no good here – There
will always be at least one blank row maybe more.
Solution 1 – I have used an array formula to create a list without spaces
and use an offset function to pick up the list. This work fine. I have even
defined the offset function as a name so I can easily reference it. I’m using
this for data validation.
Problem 2 – data Validation with dropdown doesn’t’ provide any matching
function – its just a drop down list. Thus I have set up a way to
automatically display a combobox in the cells with data validation and have
set the listfill, size and other properties on activation. Again this works
fine, except, by referencing a range of formulae instead of just raw data,
the list matching stops working. The list displays but there is no
autocomplete. ON further research, autocomplete fails if you reference a
range of formulae rather than raw data. Or perhaps I should say complex
formula . Actually, if you reference the complex formula range to another
range on a different sheet using a simple “=cellref†then it works again
however that is very impractical.
Solution 2 – Load the data into an array and then set the list from the array.
Problem 3 – that doesn’t work either. The autocomplete fails when you load
from an array.
I’ve got to the end of my self discovery and am wondering if anyone can shed
some light or offer alternative suggestions. Its so frustrating as it would
make such a great solution and I feel that it should work and am so close but
can’t quite get there.