This Question involves knowledge of Excel 2000

[mchow2469]

Hi,
I currently have a combobox which I want to add items on a spreadsheet in Excel 2000. I read everywhere that when you right click the combobox, go into "format control", there should be a control tab, where you can enter the range of items to add.

There is no control tab when I enter "format control". I can always use .additem in VBA, but I want to do it in the excel which is easier.
Anyone have this problem also?

I know this is the wrong thread to post it in, but I am too lazy to register at another forum, since I believe people with VBA knowledge are familar with Excel too.

[RobDog888]

All you get is "Size", "Protection", "Properties", and "Web" from the Format Control menu item.
Use the ListFillRange property in the property window to designate a cell range of values to use for the items.

[mchow2469]

Thanks
Even on the helpfile included with the Excel says use the control tab. Anyways that property requires a range. The data I want to add in are in non adjacent cells. For example, R5C26, R5C36, etc.

How would I do this?

[RobDog888]

Create a new Range that contains linked cells to those non-adjacent cells. Then set the cbo property to use the new range which is just gathering up the non-adjacent cells for use in the cbo.

[mchow2469]

Thank you Rob for the helpful advice. I was wondering can you reference a range from another sheet for the listfillrange property? So far from all my tests, I can not.

[mchow2469]

nevermind, I found a way. Just insert|name|define the names and then just set listfillrange property to it. Thanks anyways!

[RobDog888]

Oops, sorry for the delay. I was busy in Chit Chat Forum most of the morning.

You can even reference ranges from other workbooks by identifing the workbook name and then an "!" followed by the named range. Although, Excel actually opens the referenced workbook behind the scenes.