Form control buttons.

Question

Using form Control Buttons to return values to single cell. Getting unstable response. Expecting 1,2,3... getting 1,4,5 to $H9.

Driving me nuts.

Answer 1

Appreciate your response.

Thanks.

Answer 2

Form controls return a number based on their linked cell and configuration, so unexpected values (like 1,4,5 instead of 1,2,3) usually come from how the controls are linked or how the INDEX/lookup logic is set up.

Check these points step by step:

1. Verify each button’s linked cell
   • Select each Form control (button, list box, combo box, etc.).
   • Right‑click → Format Control → Control tab.
   • Confirm Cell link is the same cell (for example, $H$9) if all buttons are supposed to write to a single cell.
   • If different buttons are meant to write different values, ensure each has the correct cell link.
2. Confirm the returned value type
   • For list boxes and combo boxes, the linked cell stores the position (1, 2, 3, …) of the selected item, not the item itself.
   • If the list range has changed (items added/removed/reordered), the positions will change, so the same selection may now return 4 or 5 instead of 2 or 3.
   • Re‑check the Input range in Format Control → Control and make sure it matches the intended list.
3. Check the INDEX (or similar) formula
   • A common pattern is: =INDEX(H1:H20, G1, 0) where G1 is the linked cell.
   • If the linked cell is $H$9, ensure the formula references that exact cell and the list range is correct.
   • If the list range or linked cell changed, update the formula accordingly so that the numeric index maps correctly.
4. Ensure only one control is driving the cell at a time
   • If multiple controls share the same cell link (e.g., several list boxes or spinners all writing to $H$9), they can overwrite each other and appear “unstable.”
   • Either:
     • Give each control its own linked cell and then use formulas to combine results, or
     • Use only one control per linked cell.
5. Check for accidental ActiveX vs Form control mix
   • Use only Form controls for this scenario if the goal is to “easily reference and interact with cell data without using VBA code.”
   • Mixing ActiveX controls and Form controls can cause unexpected behavior if both try to manipulate the same cells.

If all controls are correctly linked and the list/INDEX formula is consistent, the linked cell should reliably return 1, 2, 3, … according to the selected item’s position.

---

References:

• How to use the forms controls on a worksheet in Excel
• Form controls
• Overview of forms, Form controls, and ActiveX controls on a worksheet
• XlFormControl enumeration (Excel)
• Assign a macro to a Form or a Control button