PDA

View Full Version : Combo Boxes and Excel 2000



Raymond
12-07-2002, 12:22 PM
I was wondering if anyone out there would be able to help me.

I have an excel spreadsheet with a named range for a combo box on another sheet. I wanted to be able to update the second spreadsheet based on the name selected in the combo box, but I have been unable to at this point in time. Is this possible, and how do you do it?

Russell D
12-07-2002, 03:02 PM
A bit clearer explanation is required Raymond.

Is it a range name you select in the combobox list?
What exactly do you want to have happen once a combobox selection is made?

parry
12-07-2002, 04:22 PM
Hi, like Russell I'm a bit hazy on what you mean. I can tell you how I manage combo boxes and hope this works for you. Once you have typed in the range of cell you want to appear in the drop-down box (you seem to have named a range for this).

You get different results if you draw the combobox from the Forms toolbar as opposed to the Visual Basic toolbar, so I'll explain this as if you have drawn the combobox from the VB toolbar.

Right click the ComboBox and select properties. Scroll down until you see LinkedCell then enter the range for the values to appear in the dropdown box (this will be the name of your range or just the range like a1:a5). Next, in ListFillRange enter the cell where you want the selected value from the ComboBox to be placed.

Now you can have formulas in different sheets refering to the value in the cell refered to in ListFillRange and these effect your formulas as the ComboBox value changes. As a tip, draw the ComboBox over the cell where you have the ListFillRange and this hides that cell from prying eyes.

For example, say you have a formula in sheet 2 where you want 10 X [value in comboBox], where you have a cComboBox with ListFillRange = sheet1, cell a3 & values 1-10 in the Combo. In sheet 2 you would enter .... =Sheet1!a10*10. As the ComboBox values change (1-10), the formula result will change dependant upon the value in the comboBox selected.

hth
Parry

parry
12-07-2002, 04:25 PM
Opps formula should read =Sheet1!a3*10.