Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combo box value does not update in Excel 2007
I inserted a combo box form control in a cell and found problems with
changing the value. Another cell on the worksheet contains a formula using the value in the combo box. When I selected a new value in the combo box and recalculated the formula it used the old value in the combo box rather than the new one. I noticed that when I used the arrow keys to navigate back to the cell containing the combo box the formula bar showed the old value, even though the combo box in the cell showed the new value. Am I doing something wrong, or is Excel not behaving as it should? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combo box value does not update in Excel 2007
bump
"Shaun" wrote: I inserted a combo box form control in a cell and found problems with changing the value. Another cell on the worksheet contains a formula using the value in the combo box. When I selected a new value in the combo box and recalculated the formula it used the old value in the combo box rather than the new one. I noticed that when I used the arrow keys to navigate back to the cell containing the combo box the formula bar showed the old value, even though the combo box in the cell showed the new value. Am I doing something wrong, or is Excel not behaving as it should? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combo box value does not update in Excel 2007
The solution depends on whether you are using the combobox control from the Forms controls or the ActiveX controls. For a FROMS Combobox First. create in a range of cells a list of values that are to be displayed in the combobox, say B5:B9. Drop the combobox on the worksheet, right-click it, and choose Format Control. On the Control tab, enter B5:B9 in the Input Range box and put C5 in the Cell Link box. Now, when you select and item in the combobox, the result is put in C5. The result is the 1-based index of the selected item in the combobox. So, if you select the third item, the result in C5 is 3. To get the actual selected value, use a formula like =OFFSET(B5,C5-1,0,1,1) You can then use that value in other formulas. For an ACTIVEX Combobox Using the same list of values in B5:B9, drop the combobox on the worksheet, right-click and choose Properties. In that box, enter B5:B9 as the ListFillRange and enter C11 as the LinkedCell. The result of an ActiveX combobox is the actual selected value, not an index into the list, so you can use the value C11 in other formulas. I can't replicate any of the problems you describe unless the Calculation mode is set to Manual. Change it to Auitomatic and things should work fine. Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Sat, 2 Jan 2010 01:14:02 -0800, Shaun wrote: I inserted a combo box form control in a cell and found problems with changing the value. Another cell on the worksheet contains a formula using the value in the combo box. When I selected a new value in the combo box and recalculated the formula it used the old value in the combo box rather than the new one. I noticed that when I used the arrow keys to navigate back to the cell containing the combo box the formula bar showed the old value, even though the combo box in the cell showed the new value. Am I doing something wrong, or is Excel not behaving as it should? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combo box value does not update in Excel 2007
Thanks Chip,
You correctly deduced I was using the forms combobox. I have a connected problem. I want to create a table with an identical drop down box in each row of one of the columns. I have set the cell link box to the cell that the control box is in, eg for c5: Input Range = Sheet2!$B$5:$B$9 Cell Link = C5 The problem is that when I copy the contens of c5 to another cell the Cell Link value doesn't change: ie, when I copy C5 into C6, the Cell Link value doesn't change to C6 as it would if the reference was contained in a formula. How do I fill a column with similarly formatted cells, each with the correct (self-referrential) Cell Link value? "Chip Pearson" wrote: The solution depends on whether you are using the combobox control from the Forms controls or the ActiveX controls. For a FROMS Combobox First. create in a range of cells a list of values that are to be displayed in the combobox, say B5:B9. Drop the combobox on the worksheet, right-click it, and choose Format Control. On the Control tab, enter B5:B9 in the Input Range box and put C5 in the Cell Link box. Now, when you select and item in the combobox, the result is put in C5. The result is the 1-based index of the selected item in the combobox. So, if you select the third item, the result in C5 is 3. To get the actual selected value, use a formula like =OFFSET(B5,C5-1,0,1,1) You can then use that value in other formulas. For an ACTIVEX Combobox Using the same list of values in B5:B9, drop the combobox on the worksheet, right-click and choose Properties. In that box, enter B5:B9 as the ListFillRange and enter C11 as the LinkedCell. The result of an ActiveX combobox is the actual selected value, not an index into the list, so you can use the value C11 in other formulas. I can't replicate any of the problems you describe unless the Calculation mode is set to Manual. Change it to Auitomatic and things should work fine. Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Sat, 2 Jan 2010 01:14:02 -0800, Shaun wrote: I inserted a combo box form control in a cell and found problems with changing the value. Another cell on the worksheet contains a formula using the value in the combo box. When I selected a new value in the combo box and recalculated the formula it used the old value in the combo box rather than the new one. I noticed that when I used the arrow keys to navigate back to the cell containing the combo box the formula bar showed the old value, even though the combo box in the cell showed the new value. Am I doing something wrong, or is Excel not behaving as it should? . |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combo box value does not update in Excel 2007
bump
"Shaun" wrote: Thanks Chip, You correctly deduced I was using the forms combobox. I have a connected problem. I want to create a table with an identical drop down box in each row of one of the columns. I have set the cell link box to the cell that the control box is in, eg for c5: Input Range = Sheet2!$B$5:$B$9 Cell Link = C5 The problem is that when I copy the contens of c5 to another cell the Cell Link value doesn't change: ie, when I copy C5 into C6, the Cell Link value doesn't change to C6 as it would if the reference was contained in a formula. How do I fill a column with similarly formatted cells, each with the correct (self-referrential) Cell Link value? "Chip Pearson" wrote: The solution depends on whether you are using the combobox control from the Forms controls or the ActiveX controls. For a FROMS Combobox First. create in a range of cells a list of values that are to be displayed in the combobox, say B5:B9. Drop the combobox on the worksheet, right-click it, and choose Format Control. On the Control tab, enter B5:B9 in the Input Range box and put C5 in the Cell Link box. Now, when you select and item in the combobox, the result is put in C5. The result is the 1-based index of the selected item in the combobox. So, if you select the third item, the result in C5 is 3. To get the actual selected value, use a formula like =OFFSET(B5,C5-1,0,1,1) You can then use that value in other formulas. For an ACTIVEX Combobox Using the same list of values in B5:B9, drop the combobox on the worksheet, right-click and choose Properties. In that box, enter B5:B9 as the ListFillRange and enter C11 as the LinkedCell. The result of an ActiveX combobox is the actual selected value, not an index into the list, so you can use the value C11 in other formulas. I can't replicate any of the problems you describe unless the Calculation mode is set to Manual. Change it to Auitomatic and things should work fine. Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Sat, 2 Jan 2010 01:14:02 -0800, Shaun wrote: I inserted a combo box form control in a cell and found problems with changing the value. Another cell on the worksheet contains a formula using the value in the combo box. When I selected a new value in the combo box and recalculated the formula it used the old value in the combo box rather than the new one. I noticed that when I used the arrow keys to navigate back to the cell containing the combo box the formula bar showed the old value, even though the combo box in the cell showed the new value. Am I doing something wrong, or is Excel not behaving as it should? . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 Combo Box questions | Excel Discussion (Misc queries) | |||
Combo box to link to SQL Database table in Excel 2007 | Excel Discussion (Misc queries) | |||
Combo box update to field | Excel Discussion (Misc queries) | |||
forcing excel to update the Cell Link when copying Combo Boxes | Excel Worksheet Functions | |||
Cant get a combo box to update without closing form | Excel Discussion (Misc queries) |