Como Box Item Sequencing

Posted by Michele on August 01, 2001 2:23 AM

The ADDITEM Method is very usefull to add items to a Combo Box but unfortunatey it just adds the item at the end of the list making a visual look-up by the user rather difficulte and not very practical. Is it possible to sort this list?

I have made my own range table (named TBL) and linked the Combo Box to this table. This way I can have my own code to add an item and sort the table. The problem is that the Combo Box then displays 1 item less for every item added to the table until a fresh open of the Excell file.

Thanks for your help!
Michele

Posted by Aladin Akyurek on August 01, 2001 2:34 AM

Wouldn't a named dynamic range work? If TBL is a ordinary named range, you can make it dynamic.

Aladin

Posted by Michele on August 02, 2001 4:27 AM

Thanks Aladin but how do we make this Dynamic? I've tried several things but no success.
Greatful
Michele

Posted by Aladin Akyurek on August 02, 2001 5:10 AM

Activate the first cell of your TBL list.Lets say the first cell is A2 on sheet, say, X.
Activate Insert|Name|Define.

(1) Select the name TBL from the list(I'm assuming this to already exist; if not, see below).
(2) Enter as the value of Refers to:

=OFFSET(X!$A$2,0,0,COUNTA($A:$A),1)

This formula requires that you have nothing but your TBL items.

If you do not have TBL in the list, just enter TBL as value of Names in the workbook.

You can use this name anywhere you want,I believe even in a macro or other VBA code.

Aladin

Posted by Michele on August 02, 2001 6:42 AM

Aladin,
Thanks again but it doesn't work. There are several problems:
1)It defines (and displays in the combobox) only column A and I have Columns A & B in my table.
2)It doesn't allow any selecting from the ComboBox. The box opens up but selections can't be made - it's frozen!
3)The combobox doesn't display all the items.
I'm open for more suggestions if you have any.
Thanks,
Michele

Posted by Michele on August 02, 2001 7:11 AM

Aladin,
I have resolved the problem. After adding an entry to the table - the integrity of the name TBL is kept - but I figured that the ComboBox doesn't know the new dimensions. So I refresh the CombBox's info by issuing:
ComboBox2.ListFillRange = "Tbl"
Then, when the Comobox is accessed, it displays every item & both columns, every item is in sequence (I sort the table after adding to it) and selections can be made. I do the same when deleting or editing an item.
(Sorry if I never mentioned this was in VB Procedures).
Many thanks for your help.
Michele