Scroll bar and spin button form controls

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. A scroll bar form control is linked to cell H1. What does the scroll bar change when you move it?

A The number stored in cell H1

B The font size of the chart title

2. Which setting stops a scroll bar's number going below 2015?

A Maximum value = 2015

B Minimum value = 2015

3. You want a scroll bar to move through the years 2015 to 2024 one year at a time. Which settings are correct?

A Minimum 2015, Maximum 2024, Incremental change 1

B Minimum 2024, Maximum 2015, Incremental change 10

4. A spin button is linked to cell H1 with minimum 1, maximum 5 and incremental change 1. H1 shows 5. What happens when you click the up arrow?

A H1 changes to 6

B H1 stays at 5, because it is the maximum

5. How is a spin button different from a scroll bar?

A It has up and down arrows but no slider, and it still changes a linked cell

B It changes the chart directly without using a linked cell

Match the Pairs

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

Left column		Right column
1Minimum value: 2018, Maximum value: 2024		AThe minimum is bigger than the maximum, so the control cannot count through the years. The minimum must be the smaller number.
2Incremental change: 1		B=INDEX(B2:B8,$H$1)
3A cell link makes the control write its current number into one cell, so other formulas can read it.		CSets the lowest and highest numbers the scroll bar or spin button can put in the linked cell, such as the first and last year.
4The linked number can be used with INDEX to pick the matching row, so the chart shows the chosen year.		DEach click on the arrow of a scroll bar or spin button moves the linked number by 1, so the chart steps one year at a time.
5Minimum value: 2024, Maximum value: 2018		ECell link: $H$1
6Cell link: (blank), chart reads =INDEX(B2:B8,$H$1)		FNo cell link has been set, so the control has no cell to write its number into. INDEX reads an empty cell and the chart never changes.

Definitions

Scroll bar

Fill in the Blanks

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

=_____(B2:B11,$E$1)

Cell link: $E$1 =INDEX(B2:B11,____)

Data rows: B2:B11 (10 years) Minimum value: 1 Maximum value: __

Show every 2nd year only Incremental change: _

Data in A2:A8 (7 years) Minimum value: _

Spot the Error

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

Question 1

A Scroll bar settings

B Minimum value: 2030

C Maximum value: 2024

D Incremental change: 1

E Cell link: $H$1

Question 2

A Spin button settings

B Minimum value: 1

C Maximum value: 0

D Incremental change: 1

Question 3

A Scroll bar min: 1

B Scroll bar max: 10

C Cell link: $H$1

D =INDEX($H$1,B2:B11)

Question 4

A Spin button settings

B Cell link: $H$1:$H$5

C Maximum value: 2024

Question 5

A Minimum value: 2015.5

B Maximum value: 2024

C Incremental change: 1

Answer Key

Scroll bar and spin button form controls

Section A: Quick Questions

A — The number stored in cell H1

B — Minimum value = 2015

A — Minimum 2015, Maximum 2024, Incremental change 1

B — H1 stays at 5, because it is the maximum

A — It has up and down arrows but no slider, and it still changes a linked cell

Section B: Match the Pairs

Minimum value: 2018, Maximum value: 2024 → Sets the lowest and highest numbers the scroll bar or spin button can put in the linked cell, such as the first and last year.

Incremental change: 1 → Each click on the arrow of a scroll bar or spin button moves the linked number by 1, so the chart steps one year at a time.

A cell link makes the control write its current number into one cell, so other formulas can read it. → Cell link: $H$1

The linked number can be used with INDEX to pick the matching row, so the chart shows the chosen year. → =INDEX(B2:B8,$H$1)

Minimum value: 2024, Maximum value: 2018 → The minimum is bigger than the maximum, so the control cannot count through the years. The minimum must be the smaller number.

Cell link: (blank), chart reads =INDEX(B2:B8,$H$1) → No cell link has been set, so the control has no cell to write its number into. INDEX reads an empty cell and the chart never changes.

Section C: Definitions

Scroll bar — A form control that changes a linked cell's number between a minimum and maximum, in steps, so a chart can move through values like years. A spin button does the same.

Section D: Fill in the Blanks

=INDEX(B2:B11,$E$1)

Cell link: $E$1
=INDEX(B2:B11,$E$1)

Data rows: B2:B11 (10 years)
Minimum value: 1
Maximum value: 10

Show every 2nd year only
Incremental change: 2

Data in A2:A8 (7 years)
Minimum value: 1

Section E: Spot the Error

B — The minimum must be lower than the maximum. A minimum of 2030 is above the maximum of 2024, so the scroll bar can't move through the years. It should be 'Minimum value: 2015'.

C — The maximum is lower than the minimum of 1, so the spin button has no range to move through. It should be a number above the minimum, such as 'Maximum value: 10'.

D — The INDEX arguments are in the wrong order. The range comes first and the row number (the linked cell) comes second: =INDEX(B2:B11,$H$1).

B — The cell link must be one single cell, because the control puts one number into it. A range like $H$1:$H$5 is not allowed. It should be 'Cell link: $H$1'.

A — Scroll bar and spin button settings only accept whole numbers, so a decimal like 2015.5 is not allowed. It should be 'Minimum value: 2015'.