Option buttons and combo boxes as form controls

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. Where do you find the option button and combo box form controls in Excel?

A On the Home tab, next to the Font group

B On the Developer tab, once you have turned it on in Options

2. What does the cell link of a form control do?

A It stores the user's choice in a chosen cell

B It links the control to another workbook on the internet

3. A student clicks the 2nd option button in a group. What number appears in the linked cell?

A 2

B The text of the button's label

4. You have two sets of option buttons on one sheet, and each set should work on its own. What do you use?

A A second Developer tab

B A group box around each set of option buttons

5. Which setting fills a combo box with a list of choices?

A Input range, such as the cells A2:A5

B Cell link, such as the cell E1

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

Left column		Right column
1Cell link: $H$1		AThe list of items that a combo box shows in its drop-down
2Input range: $A$2:$A$5		BThe cell where a form control saves the number of the choice the user made
3A combo box stores the position of the chosen item in its linked cell. INDEX turns that number back into the item's name.		CWithout a group box around each set, all the buttons join one group, so picking in one set deselects the other and they share one cell link.
4Option buttons placed inside the same group box share one cell link, so only one can be selected at a time.		DThe formula reads J1, but the control writes its choice to H1, so the result never changes when the user picks something.
5Cell link: H1 and =INDEX(B2:B5,J1)		EGroup box > Option button 1, 2, 3 > Cell link $H$1
6Two sets of option buttons on one sheet, no group boxes		F=INDEX($A$2:$A$5,$H$1)

Definitions

form control

Fill in the Blanks

Complete each line of code by filling in the missing parts.

Customize Ribbon > tick _________ Insert > Form Controls > Option Button

Combo box cell link: $H$1 =INDEX(B2:B9,____)

Draw a _________ first, then draw the option buttons inside it

Format Control > ___________: A2:A6 Format Control > Cell link: H1

Option button 1 is linked to H1 =IF(H1=_,"Maths","English")

Spot the Error

Shade in the letter of the line that contains an error.

Question 1

A Right-click > Format Control

B Click the Control tab

C Cell link: $B$1

D =CHOOSE($B$2,"Maths","Art")

Question 2

A Options > Formulas

B Tick Developer

C Click OK

D Developer tab appears

Question 3

A Home > Insert > Combo Box

B Input range: $E$2:$E$5

C Cell link: $B$1

Question 4

A Draw a Group Box

B Draw Option Button 1 inside

C Draw Option Button 2 outside

D Set one cell link: $B$1

Question 5

A Cell link: $B$1

B Button 1 chosen: B1 is 1

C Button 2 chosen: B1 is 2

D =IF($B$1=1,"Cats","Dogs"

Answer Key

Option buttons and combo boxes as form controls

Section A: Quick Questions

B — On the Developer tab, once you have turned it on in Options

A — It stores the user's choice in a chosen cell

A — 2

B — A group box around each set of option buttons

A — Input range, such as the cells A2:A5

Section B: Match the Pairs

Cell link: $H$1 → The cell where a form control saves the number of the choice the user made

Input range: $A$2:$A$5 → The list of items that a combo box shows in its drop-down

A combo box stores the position of the chosen item in its linked cell. INDEX turns that number back into the item's name. → =INDEX($A$2:$A$5,$H$1)

Option buttons placed inside the same group box share one cell link, so only one can be selected at a time. → Group box > Option button 1, 2, 3 > Cell link $H$1

Cell link: H1 and =INDEX(B2:B5,J1) → The formula reads J1, but the control writes its choice to H1, so the result never changes when the user picks something.

Two sets of option buttons on one sheet, no group boxes → Without a group box around each set, all the buttons join one group, so picking in one set deselects the other and they share one cell link.

Section C: Definitions

form control — A Developer tab control, such as an option button or combo box, that writes the user's choice into a linked cell so formulas and charts can react.

Section D: Fill in the Blanks

Customize Ribbon > tick Developer
Insert > Form Controls > Option Button

Combo box cell link: $H$1
=INDEX(B2:B9,$H$1)

Draw a Group Box first, then draw the
option buttons inside it

Format Control > Input range: A2:A6
Format Control > Cell link: H1

Option button 1 is linked to H1
=IF(H1=1,"Maths","English")

Section E: Spot the Error

D — The formula must read the cell that the control writes to. The cell link is $B$1, so CHOOSE should use $B$1, not $B$2.

A — The Developer tab is turned on in Options > Customize Ribbon, where you tick Developer. The Formulas settings do not add it.

A — Form controls such as the combo box are on the Developer tab. The Home tab does not have them, so it should be Developer > Insert > Combo Box.

C — Option buttons must be drawn inside the group box to belong to the same group. Drawn outside, button 2 would not work with button 1, so it should be 'inside'.

D — The IF formula is missing its closing bracket. It should end with ) so it reads =IF($B$1=1,"Cats","Dogs").