Using Form
Controls in Excel: Option Buttons
The
option buttons usually act as groups of buttons where, if one button in a group
is selected, all the others of the same group are deselected. The values returned
by option buttons depend on the order in which they are created - the first
returns the value 1, the second 2 and so on. To set the linked cell of the option
button and to change the text displayed, right-click the button after it has
been created. The buttons can be moved around by dragging the edge of the rectangle
when it is selected; similarly a button can be deleted by clicking on the edge
of the rectangle and pressing the Delete key on the keyboard (or Cut from the
menu).
One important point
to remember is that all buttons of the same group change the value in a single
linked cell. In other words, three option buttons linked to cell A5
will change the contents of A5 to 1, 2 and 3 respectively. But what happens
if you want to use values other than 1, 2 and 3?
The CHOOSE
function
The
CHOOSE function can take the values returned from Option Buttons
and basically convert them into 1st choice, 2nd choice and so on. In the image
here, the value in cell A2 is currently 3 because Option Button
3 is selected. The formula in cell A5 is as follows: =CHOOSE(A2,"Alan","Brian","Carol")
or choose the third value in the list because A2 is set to 3 (note
that text items should be enclosed in speech marks). Obviously, the CHOOSE function
can also take a list of numerical values, e.g. =CHOOSE(A2,0.5,10,17.5),
then Option Button 3 would return the value 17.5 in this instance.
The CHOOSE() function
can be extended to perform extra operations, or embedded into IF statements.
As an example, a simple function machine could be created by tagging arithmetic
operations onto the end, i.e. =CHOOSE(A2,0.5,10,17.5)*4+1 or, if there
was a spinner controlled value in A3, =CHOOSE(A2,0.5,10,17.5)*A3.
More than
one group of Option Buttons
If
you need two or more groups of Option Buttons, then each group needs to be enclosed
within a Group Box. In fact, it is preferable to create the
Group Boxes first, then create the Option Buttons within each box. Each group
of buttons must link to the same cell, then the values in the cell can be used
to select one from a list of values from the CHOOSE function.
Option
Buttons Task
Create a spreadsheet which provides a quick way of working out monthly repayments
on three different loans (£10,000 - £25,000 - £60,000) at
three different interest rates (2.5% - 6% - 9.75%) over three different periods
(1 year - 5 years - 12 years). The function to use will be PMT - Premium, Month,
Term.
Options
Buttons Task 2
Use the same task as above, but set a range of Loan Amounts which are varied
by a Scroll Bar Control moving in steps of 100.