Anyone know... Combo Boxes in Excel 2003?

[Taem]

Hello Lurkers,

I was wondering if anyone here knew how to edit a combo box in excel 2003 so that when you click on the arrow (or bullet) you can have multiple listings drop downs to choose from. I tried opening up the properties, but my mind suddenly went blank. I knew I had seen that "properties" set up before and it just dawned on me it was from my Visual Basic class, but I'm doing Excel, right? Anyways, I forgot almost everything from Visual Basic so I may have to re-read my notes unless there is a simpler solution in Excel.

Basically, I'm making an Excel sheet that calculates my eBay listing fees. I have a column that says STARTING AMOUNT BELOW and I want the combo box to list the ebay break points for the listing fee with reference to the fee amount, thus when you select say $9.99 or below, it adds a $0.35 inserting fee to the list of possible fees.

Then I want a simple 'yes and no' combo or list box that says Buy It Now [combo box 'yes' or 'no'] and if no leave blank, if yes $0.10, etc.

I don't know if that makes any sense, but if it does, I hope someone here knows the answer. Thanks for any replies ahead of time.

[NiteFox]

Oh, Christ on a crutch, I know of this. Did the exact same thing for my GCSE Information Technology project. Back with Excel 95.

If I could find my own notes I'd be of some help, but... Sorry.

Now you've got me all curiositirised. Mind if I piggyback on this query?

[Taem]

Mind if I piggyback on this query?

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

[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?

[Nvts]

Edit: Heh. xposted :P



I have a sheet set up that has a dropdown, but does not use a combo box, but it works really well for what I do, and sounds like it might work for you as well.

Look into Data Validation. I have a cell (A2) set up as

Data Validation--
Allow : List
Source : =$E$1:$E$9 ( where my list of choices are)


E1:E9 has my long error codes in English that users can read and understand. “The user entered in an invalid value”
F1:F9 has a numeric code - 1,2,3, etc
H1:H9 has the related short error code abbreviations that no one remembers. “ID10T”


In the next cell over, I have
=IF(ISERROR((VLOOKUP(A2,$E$1:$H$9,2))),"",CHOOSE(((VLOOKUP(A2,$E$1:$H$9,2))),$H$1,$H$2,$H$3,$H$4,$H$5,$H$6,$H$7,$H$8,$H$9))

That allows me to pull the ‘short’ code (ID10T) for the longer error code selected in the dropdown.


I know it’s not exactly what you asked for, but it works well for me, and it might work well for you, or others ;)

[Walkiry]

Amazing, this thread has been up for two days without anyonke knowing an answer, and two get posted with one minute between them. What are the odds? :lol:

[Nvts]

Odds… really really good. It’s Monday morning US time. We came back to work ;)

[Walkiry]

Odds…  really really good.  It’s Monday morning US time.  We came back to work ;)

Ah, very good point.

So, MEAT, do you really trust the answers of these slackers? :P ;)

[Jeunemaitre]

Ah, very good point.

So, MEAT, do you really trust the answers of these slackers? :P ;)

Hey, just because I check an internet message board 1st thing in the morning instead of CNN.com doesn't make me a slacker. However, checking it again during my mid morning coffee break might...

Regardless, Nvts is right, I don't check the LL fora over the week-end, and even if I did, I don't think I would've answered a post on work-related content on my days off from work. The speed of the office internet connection is too much of a draw. This is also the place where I check in on Redvsblue.com, because it's the only place where downloading an episode won't take me 10 hrs.

[Deadpan]

I use Data Validation all the time. There are a few tricks you should be aware of:

Insert Name Define

Range names allow you to tuck your drop down list away on a background sheet or even in a separate workbook. Cell references only work for the active sheet.


Use MATCH and INDEX rather than VLOOKUP, HLOOKUP or LOOKUP

There's a memory leak on the LOOKUPs and you're restricted to looking up on the first column of a table in any case. It's much safer to do a MATCH to find a cross-reference then use the result to pull out the appropriate response. You don't need to remember column numbers for a start.

[Taem]

OMG, I'm so confused! Alrighty, I ditched the combo-box idea until I learn some more VB.

I used cell Validation to do a Yes/No drop down box and inserted some nice text in there with some criteria (never knew about cell validation before - fantastic tool!). Then I made an IF statement for such things as, Buy-It-Now, Gallery, Listing Designer, etc; if they said no, value was set to zero, but if yes, appropiate value was entered.

I also used the cell Validation technique to make a list for number of pictures from 0-6, and added the appropiate charge in a box.

I COULD get the cell Validation to referance my Starting Bid table way off screen in the hundreds somewhere, but could NOT get it to match up with a corresponding Listing Fee amount. I tried Vlookup, Match, If + Choose... I racked my brain. Eventually I just used a nested IF statement with multiple IF statements which seemed to work perfectly under numerous tests such as entering negative values, letters, and decimals.

So I was a little dissappointed I could not grasp how to make the drop-down box a reality, however I learned something new and made my spread sheet work, which is what I really wanted in the first place. Thanks everyone who helped me, as I would not have been able to figure this out without the responces of everyone who replied (literally, I refferenced all three of the posts which stated what I could do). Thanks again for the help. If anyone has any more ideas on the drop-down box idea and how to make a cell display a fee amount that corrosponds to the number they choose on the same row and table (I'm pretty sure it was already written, but I'm an idiot an Excel), please let me know.

[Nvts]

If anyone has any more ideas on the drop-down box idea and how to make a cell display a fee amount that corrosponds to the number they choose on the same row and table (I'm pretty sure it was already written, but I'm an idiot an Excel), please let me know.

I’m glad you got your sheet working fairly well. :)


I want to make sure what you are asking for before attempting to answer this last bit. Please elaborate what you are still needing.

[Taem]

Okay. eBay charges an 'insertion fee' based on how much the starting price of the item is. It is not a calculated rate so I can't just multiple the starting price by 'x'%. It uses a table with specific break points, at which point a fee is included:

http://pages.ebay.com/help/sell/fees.html

So what I'm trying to do is have a cell with a drop down option that says something like <= $0.99, <= $9.99, <= $24.99, etc., in the drop down box. Then in a separate cell have the corresponding 'fee' calculated based on what is chosen.

Only after a few hours did I realized I didn't need a drop down box for the minimum selling price. This allowed for more flexibility as the user could enter whatever minimum rate they desired and the nested 'if' statements would give the appropriate fee. I just wonder if there is a better way to retrieve the data by matching up what the user typed in for a minimum selling price with the fees chart without having to make a huge nested 'if' statement. I tried placing the minimum selling price on A100-A106 and the fee on B100-B106 and tried everything to get it to say, alright we got $50.00 entered, so the corresponding fee on the next column, same row is... but could not figure out how to do it right.

[Nvts]

Cell A2 = your Item Price
Cell B2 = the fee (=VLOOKUP(A1,$E$1:$F$8,2))

Cells E1 to F8 the fee chart


0       0
0.01    0.3
1       0.35
10      0.6
25      1.2
50      2.4
200     3.6
500     4.8


Hope that works out for you.


Edit: why that works....

(excel help file)

Range_lookup is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, VLOOKUP will find an exact match. If one is not found, the error value #N/A is returned.

Remarks

If VLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the largest value that is less than or equal to lookup_value.