Excel ActiveX Combo - AutoComplete and LIst Issues
 

I have gone around the hoop with testing the combo box - ActiveX control in
Excel 2003.
What Im 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. Im using
this for data validation.

Problem 2 €“ data Validation with dropdown doesnt 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 doesnt work either. The autocomplete fails when you load
from an array.

Ive 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
cant quite get there.

Excel ActiveX Combo - AutoComplete and LIst Issues
 

Hi James,
Take a look at Debra Web
http://www.contextures.com/xlDataVal02.html#TwoWord

"James Buist" wrote:

I have gone around the hoop with testing the combo box - ActiveX control in
Excel 2003.
What Im 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. Im using
this for data validation.

Problem 2 €“ data Validation with dropdown doesnt 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 doesnt work either. The autocomplete fails when you load
from an array.

Ive 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
cant quite get there.

Excel ActiveX Combo - AutoComplete and LIst Issues
 

Thanks for the link. I have been doing all that stuff for years. I use
similar techniquest to simplify the lists and get rid of blanks and do
matching and duplicate highlighting etc. No, the problem is with Excel. It
doesn't seem to be able to handle much more than simple lists with
automatching working!
It seems pretty bad that you can't load the list from an array and have the
automatch work. You just get a normal combo with no automatching when you add
from array or from lists made of fomulae like those in the link you gave.
I've tried all sorts of combinations.

Thanks anyway




"Eduardo" wrote:

Hi James,
Take a look at Debra Web
http://www.contextures.com/xlDataVal02.html#TwoWord

"James Buist" wrote:

I have gone around the hoop with testing the combo box - ActiveX control in
Excel 2003.
What Im 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. Im using
this for data validation.

Problem 2 €“ data Validation with dropdown doesnt 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 doesnt work either. The autocomplete fails when you load
from an array.

Ive 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
cant quite get there.