Combo box value does not update in Excel 2007

[Shaun]

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?

 

[Shaun]

bump

 

[Chip Pearson]

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]

 

[Shaun]

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?

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]

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?

.

 

[Shaun]

bump

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?

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]

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?

.