Anyone know... Combo Boxes in Excel 2003?

[Jeunemaitre]

Fine by me.  I just hope someone knows the answer :D .

Finally, my 5-6 hrs a day in Excel pays off where it really matters... The Lurker Lounge!

You could actually accomplish the same thing using cell validations (Data->Validation...->'Allow': List). You can enter the list of values in the box labeled source (i.e. entering "yes,no" without the quotes, produces a cell whose values are limited to yes and no, and when the cursor is in the cell, you get a drop down arrow to select a legal value). The alternative is to enter a cell range (or select one if you prefer) which allows you to change the possible values later if you decide some value has changed (a markup percentage, or whatever). Personally I'm in favor of this because it saves you from having to get into the VB Fun Stuff™, and gives you a physical cell to use as a basis for conditionals and look ups (=if(x,x,x) or =vlookup(x,x,x,T/F) or =hlookup(x,x,x,T/F)).

If you want to get into VB combo boxes, you can do the following:
1. Using the Control Toolbox (View-> toolbars-> Control Toolbox if you don't have it out already), draw in the combo box where you want it.
2. Once you've drawn it, make sure you're in design mode (the triangle/pencil button on the far left of the control toolbox will indicate whether you are or not (it's a simple toggle button). Then right click on the box and choose properties (or hit the toolbar button showing a hand holding an index card).
3. In the properties box, specify a 'LinkedCell' where you want the value of the combo box to be displayed on the Excel sheet (otherwise the value of the combo box will exist only as combobox1.value, which I'm not sure can be used as an argument in conditionals and lookups. If it can, somebody please let me know...)
4. On the next line down in the properties box, specify a 'ListFillRange'. As far as I know, this must be a contiguous range on the same sheet, or a variable name (which currently exists as a contiguous range). This range contains the possible values for the box to take, and the items in this range can be changed if needed to reflect a change in policy.
5. Exit Design mode (toggle the triangle/pencil button) and test your new combo box.

There are more fun things you can do, like hiding ranges, sheets, or controls, or reformatting ranges, or whatever, but then you get into VB code that resides in the module behind the button. If you want to do fun stuff like that, let me know and I can go into more detail about it. As I said, personally, I prefer the validation method because your value resides in the cell itself, rather than the VB control (either of which can be accessed for other code purposes (sheet.range().value or sheet.combobox1.value). If you need an example posted, I can upload an excel file with an example of each. Let me know...

edit - "pays on?", I mean really, "pays on?" What am I thinking?